from flask import Flask, request, jsonify, render_template, send_from_directory, Response
import os
import json
import sqlite3
from datetime import datetime
import random
import csv
import io
import calendar

app = Flask(__name__, static_folder='.', static_url_path='')
# 数据库文件绝对路径，存放在项目目录的 data 文件夹下
BASE_DIR = os.path.abspath(os.path.dirname(__file__))
DATABASE = os.path.join(BASE_DIR, 'data', 'supermarket.db')

# 扣款规则文件路径
DEDUCT_RULE_FILE = os.path.join(BASE_DIR, 'data', 'deduct_rule.json')

# 积分规则文件路径
POINT_RULE_FILE = os.path.join(BASE_DIR, 'data', 'point_rule.json')

def get_deduct_rule():
    # 默认规则
    rule = {'late': 50, 'early': 50, 'absent': 100}
    if os.path.exists(DEDUCT_RULE_FILE):
        try:
            with open(DEDUCT_RULE_FILE, 'r', encoding='utf-8') as f:
                rule = json.load(f)
        except Exception:
            pass
    # 兼容老数据
    if 'absent' not in rule:
        rule['absent'] = 100
    return rule

def save_deduct_rule(late, early, absent):
    rule = {'late': int(late), 'early': int(early), 'absent': int(absent)}
    with open(DEDUCT_RULE_FILE, 'w', encoding='utf-8') as f:
        json.dump(rule, f, ensure_ascii=False)

def get_point_rule():
    rule = {'rate': 1}
    if os.path.exists(POINT_RULE_FILE):
        try:
            with open(POINT_RULE_FILE, 'r', encoding='utf-8') as f:
                rule = json.load(f)
        except Exception:
            pass
    return rule

def save_point_rule(rate):
    rule = {'rate': float(rate)}
    with open(POINT_RULE_FILE, 'w', encoding='utf-8') as f:
        json.dump(rule, f, ensure_ascii=False)

# 添加跨域支持
@app.after_request
def after_request(response):
    # 记录操作日志
    try:
        record_operation_log(request, response)
    except Exception:
        pass
    response.headers.add('Access-Control-Allow-Origin', '*')
    response.headers.add('Access-Control-Allow-Headers', 'Content-Type,Authorization')
    response.headers.add('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE')
    return response

def record_operation_log(req, resp):
    # 仅记录指定的 API 操作日志
    path = req.path
    method = req.method
    # 记录增删改 (POST/PUT/DELETE) 或 特定 GET 请求 (订单查询或登录)
    if not path.startswith('/api/'):
        return
    if method not in ['POST', 'PUT', 'DELETE']:
        # 允许 GET 获取订单信息和登录日志
        if not (method == 'GET' and (path.startswith('/api/sales') or path == '/api/login')):
            return
    try:
        conn = sqlite3.connect(DATABASE)
        cursor = conn.cursor()
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # 根据路径和方法映射操作类型
        if req.method == 'POST' and req.path == '/api/login':
            op_type = '登录'
        elif req.path.startswith('/api/products') and req.method in ['POST', 'PUT', 'DELETE']:
            op_type = '商品修改'
        elif req.path.startswith('/api/sales') and req.method in ['POST', 'PUT', 'DELETE']:
            op_type = '订单操作'
        else:
            op_type = '系统设置'
        # 记录请求详情
        detail = ''
        if req.method in ['POST', 'PUT']:
            try:
                detail = json.dumps(req.get_json(), ensure_ascii=False)
            except:
                detail = ''
        user = req.remote_addr or ''
        ip = req.remote_addr or ''
        result = '成功' if resp.status_code < 400 else '失败'
        cursor.execute(
            "INSERT INTO operation_logs (timestamp, op_type, detail, user, ip, result) VALUES (?, ?, ?, ?, ?, ?)",
            (timestamp, op_type, detail, user, ip, result)
        )
        conn.commit()
    except:
        pass
    finally:
        conn.close()

# 确保数据库目录存在
if not os.path.exists('data'):
    os.makedirs('data')

# 初始化数据库
def init_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 创建商品表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_code TEXT NOT NULL UNIQUE,
        product_name TEXT NOT NULL,
        category TEXT NOT NULL,
        price REAL NOT NULL,
        initial_stock INTEGER NOT NULL,
        current_stock INTEGER NOT NULL,
        supplier TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建供应商表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS suppliers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        contact TEXT,
        phone TEXT,
        address TEXT,
        create_time TEXT NOT NULL
    )
    ''')
    
    # 创建商品分类表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        description TEXT,
        create_time TEXT NOT NULL
    )
    ''')
    
    # 创建促销活动表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS promotions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        discount REAL NOT NULL,
        start_date TEXT NOT NULL,
        start_time TEXT NOT NULL,
        end_date TEXT NOT NULL,
        end_time TEXT NOT NULL,
        category TEXT NOT NULL,
        description TEXT,
        status TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建价格调整记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS price_adjustments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_code TEXT NOT NULL,
        product_name TEXT NOT NULL,
        old_price REAL NOT NULL,
        new_price REAL NOT NULL,
        adjustment_date TEXT NOT NULL,
        reason TEXT,
        operator TEXT NOT NULL,
        create_time TEXT NOT NULL
    )
    ''')
    
    # 创建仓库信息表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS warehouses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        warehouse_code TEXT NOT NULL UNIQUE,
        warehouse_name TEXT NOT NULL,
        location TEXT NOT NULL,
        area REAL NOT NULL,
        manager TEXT NOT NULL,
        contact_phone TEXT,
        status TEXT NOT NULL,
        description TEXT,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建入库记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS stock_in_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        record_number TEXT NOT NULL UNIQUE,
        product_code TEXT NOT NULL,
        product_name TEXT NOT NULL,
        warehouse_code TEXT NOT NULL,
        warehouse_name TEXT NOT NULL,
        quantity INTEGER NOT NULL,
        batch_number TEXT,
        production_date TEXT,
        expiry_date TEXT,
        supplier TEXT NOT NULL,
        operator TEXT NOT NULL,
        note TEXT,
        status TEXT NOT NULL,
        entry_date TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建出库记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS stock_out_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        record_number TEXT NOT NULL UNIQUE,
        product_code TEXT NOT NULL,
        product_name TEXT NOT NULL,
        warehouse_code TEXT NOT NULL,
        warehouse_name TEXT NOT NULL,
        quantity INTEGER NOT NULL,
        batch_number TEXT,
        out_reason TEXT NOT NULL,
        recipient TEXT NOT NULL,
        operator TEXT NOT NULL,
        note TEXT,
        status TEXT NOT NULL,
        out_date TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建库存预警设置表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS inventory_alert_settings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_code TEXT UNIQUE,
        product_name TEXT,
        category TEXT,
        min_stock INTEGER,
        max_stock INTEGER,
        alert_enabled INTEGER DEFAULT 1,
        alert_level TEXT DEFAULT '预警',
        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # 创建员工信息表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        employee_code TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        gender TEXT NOT NULL,
        department TEXT NOT NULL,
        position TEXT NOT NULL,
        hire_date TEXT NOT NULL,
        phone TEXT NOT NULL,
        email TEXT,
        emergency_contact TEXT,
        work_experience TEXT,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建操作日志表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS operation_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        timestamp TEXT NOT NULL,
        op_type TEXT NOT NULL,
        detail TEXT,
        user TEXT,
        ip TEXT,
        result TEXT NOT NULL
    )
    ''')
    
    # 创建考勤记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS attendance_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT NOT NULL,
        name TEXT NOT NULL,
        date TEXT NOT NULL,
        in_time TEXT,
        out_time TEXT,
        duration TEXT,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL,
        FOREIGN KEY(code) REFERENCES employees(employee_code)
    )
    ''')
    
    # 创建采购订单表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS purchase_orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_number TEXT NOT NULL UNIQUE,
        supplier TEXT NOT NULL,
        order_date TEXT NOT NULL,
        expected_date TEXT,
        total_amount REAL NOT NULL,
        status TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建采购订单商品明细表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS purchase_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_number TEXT NOT NULL,
        product_code TEXT NOT NULL,
        product_name TEXT NOT NULL,
        quantity INTEGER NOT NULL,
        unit_price REAL NOT NULL,
        note TEXT,
        create_time TEXT NOT NULL,
        FOREIGN KEY(order_number) REFERENCES purchase_orders(order_number) ON DELETE CASCADE
    )
    ''')
    
    # 预先添加一些分类数据
    categories = [
        ('食品', '各类食品'), 
        ('饮料', '各类饮料'), 
        ('日用品', '日常生活用品'),
        ('服装', '各类服装'),
        ('家居', '家居用品')
    ]
    
    for category in categories:
        try:
            cursor.execute(
                "INSERT INTO categories (name, description, create_time) VALUES (?, ?, ?)",
                (category[0], category[1], datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            )
        except sqlite3.IntegrityError:
            # 如果分类已存在则跳过
            pass
    
    # 预先添加一些供应商数据
    suppliers = [
        ('北京食品批发有限公司', '张三', '13800138001', '北京市朝阳区'),
        ('上海饮料供应商', '李四', '13900139002', '上海市浦东新区'),
        ('广州日用品贸易有限公司', '王五', '13700137003', '广州市天河区')
    ]
    
    for supplier in suppliers:
        try:
            cursor.execute(
                "INSERT INTO suppliers (name, contact, phone, address, create_time) VALUES (?, ?, ?, ?, ?)",
                (supplier[0], supplier[1], supplier[2], supplier[3], datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            )
        except sqlite3.IntegrityError:
            # 如果供应商已存在则跳过
            pass
    
    # 预先添加一些仓库数据
    warehouses = [
        ('WH001', '主仓库', '北京市朝阳区', 5000.0, '李明', '13811112222', '正常', '超市主要仓库'),
        ('WH002', '冷藏仓库', '北京市海淀区', 1000.0, '王刚', '13833334444', '正常', '专门存放冷冻食品'),
        ('WH003', '备用仓库', '北京市丰台区', 2000.0, '张伟', '13855556666', '维护中', '临时仓储使用')
    ]
    
    for warehouse in warehouses:
        try:
            cursor.execute(
                "INSERT INTO warehouses (warehouse_code, warehouse_name, location, area, manager, contact_phone, status, description, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (warehouse[0], warehouse[1], warehouse[2], warehouse[3], warehouse[4], warehouse[5], warehouse[6], warehouse[7], datetime.now().strftime("%Y-%m-%d %H:%M:%S"), datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            )
        except sqlite3.IntegrityError:
            # 如果仓库已存在则跳过
            pass
    
    conn.commit()
    conn.close()

# 初始化数据库
init_db()

# 路由：提供静态文件
@app.route('/')
def index():
    return app.send_static_file('index.html')

@app.route('/<path:path>')
def serve_static(path):
    return send_from_directory('.', path)

# API路由：获取所有商品
@app.route('/api/products', methods=['GET'])
def get_products():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM products ORDER BY create_time DESC")
    products = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": products})

# API路由：获取单个商品
@app.route('/api/products/<product_code>', methods=['GET'])
def get_product(product_code):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
    product = cursor.fetchone()
    
    conn.close()
    
    if product:
        return jsonify({"success": True, "data": dict(product)})
    else:
        return jsonify({"success": False, "message": "商品不存在"}), 404

# API路由：添加商品
@app.route('/api/products', methods=['POST'])
def add_product():
    data = request.json
    
    # 验证必填字段（除了商品编码，其他都是必填的）
    required_fields = ['product_name', 'category', 'price', 'initial_stock', 'supplier']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 自动生成商品编码（如果未提供）
        if 'product_code' not in data or not data['product_code']:
            cursor.execute("SELECT MAX(id) FROM products")
            max_id = cursor.fetchone()[0] or 0
            product_code = f"P{max_id+1:04d}"
        else:
            product_code = data['product_code']
            
            # 检查商品编码是否已存在
            cursor.execute("SELECT COUNT(*) FROM products WHERE product_code = ?", (product_code,))
            if cursor.fetchone()[0] > 0:
                conn.close()
                return jsonify({"success": False, "message": f"商品编码 {product_code} 已存在，请使用其他编码"}), 400
        
        # 格式化数据
        product_name = data['product_name']
        category = data['category']
        price = float(data['price'])
        initial_stock = int(data['initial_stock'])
        supplier = data['supplier']
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        
        cursor.execute(
            "INSERT INTO products (product_code, product_name, category, price, initial_stock, current_stock, supplier, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (product_code, product_name, category, price, initial_stock, initial_stock, supplier, current_time, current_time)
        )
        conn.commit()
        
        # 获取新添加的商品
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
        product = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "商品添加成功", 
            "data": product
        })
    except sqlite3.IntegrityError as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"数据库错误：{str(e)}"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新商品
@app.route('/api/products/<product_code>', methods=['PUT'])
def update_product(product_code):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查商品是否存在
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'product_name' in data and data['product_name']:
        update_fields.append("product_name = ?")
        params.append(data['product_name'])
    
    if 'category' in data and data['category']:
        update_fields.append("category = ?")
        params.append(data['category'])
    
    if 'price' in data and data['price']:
        update_fields.append("price = ?")
        params.append(float(data['price']))
    
    if 'initial_stock' in data and data['initial_stock']:
        update_fields.append("initial_stock = ?")
        initial_stock = int(data['initial_stock'])
        params.append(initial_stock)
        
        # 如果初始库存变化，同时更新当前库存
        update_fields.append("current_stock = ?")
        params.append(initial_stock)
    
    if 'supplier' in data and data['supplier']:
        update_fields.append("supplier = ?")
        params.append(data['supplier'])
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    
    # 添加产品编码到参数列表
    params.append(product_code)
    
    try:
        query = f"UPDATE products SET {', '.join(update_fields)} WHERE product_code = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的商品
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
        product = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "商品更新成功", 
            "data": product
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除商品
@app.route('/api/products/<product_code>', methods=['DELETE'])
def delete_product(product_code):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查商品是否存在
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    try:
        cursor.execute("DELETE FROM products WHERE product_code = ?", (product_code,))
        conn.commit()
        return jsonify({
            "success": True, 
            "message": "商品删除成功"
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取所有分类
@app.route('/api/categories', methods=['GET'])
def get_categories():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM categories ORDER BY name")
    categories = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": categories})

# API路由：获取单个分类
@app.route('/api/categories/<int:category_id>', methods=['GET'])
def get_category(category_id):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM categories WHERE id = ?", (category_id,))
    category = cursor.fetchone()
    
    conn.close()
    
    if category:
        return jsonify({"success": True, "data": dict(category)})
    else:
        return jsonify({"success": False, "message": "分类不存在"}), 404

# API路由：添加分类
@app.route('/api/categories', methods=['POST'])
def add_category():
    data = request.json
    
    # 验证必填字段
    required_fields = ['name']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 格式化数据
    name = data['name']
    description = data.get('description', '')
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        cursor.execute(
            "INSERT INTO categories (name, description, create_time) VALUES (?, ?, ?)",
            (name, description, current_time)
        )
        conn.commit()
        
        # 获取新添加的分类
        category_id = cursor.lastrowid
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM categories WHERE id = ?", (category_id,))
        category = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "分类添加成功", 
            "data": category
        })
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "分类名称已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新分类
@app.route('/api/categories/<int:category_id>', methods=['PUT'])
def update_category(category_id):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查分类是否存在
    cursor.execute("SELECT * FROM categories WHERE id = ?", (category_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "分类不存在"}), 404
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'name' in data and data['name']:
        update_fields.append("name = ?")
        params.append(data['name'])
    
    if 'description' in data:
        update_fields.append("description = ?")
        params.append(data['description'])
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加分类ID到参数列表
    params.append(category_id)
    
    try:
        query = f"UPDATE categories SET {', '.join(update_fields)} WHERE id = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的分类
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM categories WHERE id = ?", (category_id,))
        category = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "分类更新成功", 
            "data": category
        })
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "分类名称已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除分类
@app.route('/api/categories/<int:category_id>', methods=['DELETE'])
def delete_category(category_id):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查分类是否存在
    cursor.execute("SELECT * FROM categories WHERE id = ?", (category_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "分类不存在"}), 404
    
    # 检查是否有商品使用此分类
    cursor.execute("SELECT COUNT(*) FROM products WHERE category = (SELECT name FROM categories WHERE id = ?)", (category_id,))
    if cursor.fetchone()[0] > 0:
        conn.close()
        return jsonify({"success": False, "message": "无法删除：该分类下存在商品"}), 400
    
    try:
        cursor.execute("DELETE FROM categories WHERE id = ?", (category_id,))
        conn.commit()
        return jsonify({
            "success": True, 
            "message": "分类删除成功"
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取所有供应商
@app.route('/api/suppliers', methods=['GET'])
def get_suppliers():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM suppliers ORDER BY name")
    suppliers = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": suppliers})

# API路由：添加供应商
@app.route('/api/suppliers', methods=['POST'])
def add_supplier():
    data = request.json
    # 验证必填字段
    if 'name' not in data or not data['name']:
        return jsonify({"success": False, "message": "缺少必填字段：name"}), 400
    name = data['name']
    contact = data.get('contact', '')
    phone = data.get('phone', '')
    address = data.get('address', '')
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    try:
        cursor.execute(
            "INSERT INTO suppliers (name, contact, phone, address, create_time) VALUES (?, ?, ?, ?, ?)",
            (name, contact, phone, address, current_time)
        )
        conn.commit()
        supplier_id = cursor.lastrowid
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM suppliers WHERE id = ?", (supplier_id,))
        supplier = dict(cursor.fetchone())
        return jsonify({"success": True, "message": "供应商添加成功", "data": supplier})
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "供应商名称已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新供应商
@app.route('/api/suppliers/<int:supplier_id>', methods=['PUT'])
def update_supplier(supplier_id):
    data = request.json
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    # 检查供应商是否存在
    cursor.execute("SELECT * FROM suppliers WHERE id = ?", (supplier_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "供应商不存在"}), 404
    # 构建更新字段
    update_fields = []
    params = []
    if 'name' in data and data['name']:
        update_fields.append("name = ?")
        params.append(data['name'])
    if 'contact' in data:
        update_fields.append("contact = ?")
        params.append(data['contact'])
    if 'phone' in data:
        update_fields.append("phone = ?")
        params.append(data['phone'])
    if 'address' in data:
        update_fields.append("address = ?")
        params.append(data['address'])
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    # 添加参数
    params.append(supplier_id)
    try:
        query = f"UPDATE suppliers SET {', '.join(update_fields)} WHERE id = ?"
        cursor.execute(query, params)
        conn.commit()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM suppliers WHERE id = ?", (supplier_id,))
        supplier = dict(cursor.fetchone())
        return jsonify({"success": True, "message": "供应商更新成功", "data": supplier})
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "供应商名称已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除供应商
@app.route('/api/suppliers/<int:supplier_id>', methods=['DELETE'])
def delete_supplier(supplier_id):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    # 检查是否存在
    cursor.execute("SELECT * FROM suppliers WHERE id = ?", (supplier_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "供应商不存在"}), 404
    try:
        cursor.execute("DELETE FROM suppliers WHERE id = ?", (supplier_id,))
        conn.commit()
        return jsonify({"success": True, "message": "供应商删除成功"})
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取所有促销活动
@app.route('/api/promotions', methods=['GET'])
def get_promotions():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM promotions ORDER BY create_time DESC")
    promotions = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": promotions})

# API路由：获取单个促销活动
@app.route('/api/promotions/<int:promotion_id>', methods=['GET'])
def get_promotion(promotion_id):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM promotions WHERE id = ?", (promotion_id,))
    promotion = cursor.fetchone()
    
    conn.close()
    
    if promotion:
        return jsonify({"success": True, "data": dict(promotion)})
    else:
        return jsonify({"success": False, "message": "促销活动不存在"}), 404

# API路由：添加促销活动
@app.route('/api/promotions', methods=['POST'])
def add_promotion():
    data = request.json
    
    # 验证必填字段
    required_fields = ['name', 'discount', 'start_date', 'start_time', 'end_date', 'end_time', 'category']
    for field in required_fields:
        if field not in data or data[field] == '':
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 格式化数据
    name = data['name']
    discount = float(data['discount'])
    start_date = data['start_date']
    start_time = data['start_time']
    end_date = data['end_date']
    end_time = data['end_time']
    category = data['category']
    description = data.get('description', '')
    
    # 确定促销活动状态
    current_time = datetime.now()
    start_datetime = datetime.strptime(f"{start_date} {start_time}", "%Y/%m/%d %H:%M")
    end_datetime = datetime.strptime(f"{end_date} {end_time}", "%Y/%m/%d %H:%M")
    
    if current_time < start_datetime:
        status = "未开始"
    elif current_time > end_datetime:
        status = "已结束"
    else:
        status = "进行中"
    
    current_time_str = current_time.strftime("%Y-%m-%d %H:%M:%S")
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        cursor.execute(
            "INSERT INTO promotions (name, discount, start_date, start_time, end_date, end_time, category, description, status, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (name, discount, start_date, start_time, end_date, end_time, category, description, status, current_time_str, current_time_str)
        )
        conn.commit()
        
        # 获取新添加的促销活动
        promotion_id = cursor.lastrowid
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM promotions WHERE id = ?", (promotion_id,))
        promotion = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "促销活动添加成功", 
            "data": promotion
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新促销活动
@app.route('/api/promotions/<int:promotion_id>', methods=['PUT'])
def update_promotion(promotion_id):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查促销活动是否存在
    cursor.execute("SELECT * FROM promotions WHERE id = ?", (promotion_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "促销活动不存在"}), 404
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'name' in data and data['name'] != '':
        update_fields.append("name = ?")
        params.append(data['name'])
    
    if 'discount' in data and data['discount'] != '':
        update_fields.append("discount = ?")
        params.append(float(data['discount']))
    
    if 'start_date' in data and data['start_date'] != '':
        update_fields.append("start_date = ?")
        params.append(data['start_date'])
    
    if 'start_time' in data and data['start_time'] != '':
        update_fields.append("start_time = ?")
        params.append(data['start_time'])
    
    if 'end_date' in data and data['end_date'] != '':
        update_fields.append("end_date = ?")
        params.append(data['end_date'])
    
    if 'end_time' in data and data['end_time'] != '':
        update_fields.append("end_time = ?")
        params.append(data['end_time'])
    
    if 'category' in data and data['category'] != '':
        update_fields.append("category = ?")
        params.append(data['category'])
    
    if 'description' in data:
        update_fields.append("description = ?")
        params.append(data['description'])
    
    if 'status' in data and data['status'] != '':
        update_fields.append("status = ?")
        params.append(data['status'])
    else:
        # 如果未提供状态，则根据日期自动计算
        # 需要先获取当前促销活动的日期信息
        if 'start_date' in data and 'start_time' in data and 'end_date' in data and 'end_time' in data:
            start_date = data['start_date']
            start_time = data['start_time']
            end_date = data['end_date']
            end_time = data['end_time']
        else:
            # 获取现有的日期信息
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("SELECT start_date, start_time, end_date, end_time FROM promotions WHERE id = ?", (promotion_id,))
            promotion_dates = dict(cursor.fetchone())
            start_date = promotion_dates['start_date']
            start_time = promotion_dates['start_time']
            end_date = promotion_dates['end_date']
            end_time = promotion_dates['end_time']
        
        # 计算状态
        current_time = datetime.now()
        start_datetime = datetime.strptime(f"{start_date} {start_time}", "%Y/%m/%d %H:%M")
        end_datetime = datetime.strptime(f"{end_date} {end_time}", "%Y/%m/%d %H:%M")
        
        if current_time < start_datetime:
            status = "未开始"
        elif current_time > end_datetime:
            status = "已结束"
        else:
            status = "进行中"
        
        update_fields.append("status = ?")
        params.append(status)
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    
    # 添加促销活动ID到参数列表
    params.append(promotion_id)
    
    try:
        query = f"UPDATE promotions SET {', '.join(update_fields)} WHERE id = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的促销活动
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM promotions WHERE id = ?", (promotion_id,))
        promotion = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "促销活动更新成功", 
            "data": promotion
        })
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "价格策略名称已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除促销活动
@app.route('/api/promotions/<int:promotion_id>', methods=['DELETE'])
def delete_promotion(promotion_id):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查促销活动是否存在
    cursor.execute("SELECT * FROM promotions WHERE id = ?", (promotion_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "促销活动不存在"}), 404
    
    try:
        cursor.execute("DELETE FROM promotions WHERE id = ?", (promotion_id,))
        conn.commit()
        return jsonify({
            "success": True, 
            "message": "促销活动删除成功"
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取价格调整记录列表
@app.route('/api/price-adjustments', methods=['GET'])
def get_price_adjustments():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取查询参数
    product_code = request.args.get('product_code')
    
    if product_code:
        cursor.execute("SELECT * FROM price_adjustments WHERE product_code = ? ORDER BY adjustment_date DESC", (product_code,))
    else:
        cursor.execute("SELECT * FROM price_adjustments ORDER BY adjustment_date DESC")
    
    adjustments = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": adjustments})

# API路由：获取单个价格调整记录
@app.route('/api/price-adjustments/<int:adjustment_id>', methods=['GET'])
def get_price_adjustment(adjustment_id):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM price_adjustments WHERE id = ?", (adjustment_id,))
    adjustment = cursor.fetchone()
    
    conn.close()
    
    if adjustment:
        return jsonify({"success": True, "data": dict(adjustment)})
    else:
        return jsonify({"success": False, "message": "价格调整记录不存在"}), 404

# API路由：添加价格调整记录
@app.route('/api/price-adjustments', methods=['POST'])
def add_price_adjustment():
    data = request.json
    
    # 验证必填字段
    required_fields = ['product_code', 'new_price', 'operator', 'reason']
    for field in required_fields:
        if field not in data or data[field] == '':
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 检查商品是否存在
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (data['product_code'],))
    product = cursor.fetchone()
    
    if not product:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    product = dict(product)
    
    # 格式化数据
    product_code = data['product_code']
    product_name = product['product_name']
    old_price = product['price']
    new_price = float(data['new_price'])
    reason = data['reason']
    operator = data['operator']
    adjustment_date = datetime.now().strftime("%Y-%m-%d")
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 添加价格调整记录
        cursor.execute(
            "INSERT INTO price_adjustments (product_code, product_name, old_price, new_price, adjustment_date, reason, operator, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (product_code, product_name, old_price, new_price, adjustment_date, reason, operator, current_time)
        )
        
        # 更新商品表中的价格
        cursor.execute(
            "UPDATE products SET price = ?, update_time = ? WHERE product_code = ?",
            (new_price, current_time, product_code)
        )
        
        conn.commit()
        
        # 获取新添加的记录
        adjustment_id = cursor.lastrowid
        cursor.execute("SELECT * FROM price_adjustments WHERE id = ?", (adjustment_id,))
        adjustment = dict(cursor.fetchone())
        
        conn.close()
        
        return jsonify({
            "success": True, 
            "message": "价格调整成功", 
            "data": adjustment
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"价格调整失败: {str(e)}"}), 500

# API路由：删除价格调整记录
@app.route('/api/price-adjustments/<int:adjustment_id>', methods=['DELETE'])
def delete_price_adjustment(adjustment_id):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查记录是否存在
    cursor.execute("SELECT * FROM price_adjustments WHERE id = ?", (adjustment_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "价格调整记录不存在"}), 404
    
    try:
        cursor.execute("DELETE FROM price_adjustments WHERE id = ?", (adjustment_id,))
        conn.commit()
        conn.close()
        return jsonify({
            "success": True, 
            "message": "价格调整记录删除成功"
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500

# API路由：获取所有仓库
@app.route('/api/warehouses', methods=['GET'])
def get_warehouses():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM warehouses ORDER BY create_time DESC")
    warehouses = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": warehouses})

# API路由：获取单个仓库
@app.route('/api/warehouses/<warehouse_code>', methods=['GET'])
def get_warehouse(warehouse_code):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM warehouses WHERE warehouse_code = ?", (warehouse_code,))
    warehouse = cursor.fetchone()
    
    conn.close()
    
    if warehouse:
        return jsonify({"success": True, "data": dict(warehouse)})
    else:
        return jsonify({"success": False, "message": "仓库不存在"}), 404

# API路由：添加仓库
@app.route('/api/warehouses', methods=['POST'])
def add_warehouse():
    data = request.json
    
    # 验证必填字段
    required_fields = ['warehouse_code', 'warehouse_name', 'location', 'area', 'manager', 'status']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 格式化数据
    warehouse_code = data['warehouse_code']
    warehouse_name = data['warehouse_name']
    location = data['location']
    area = float(data['area'])
    manager = data['manager']
    contact_phone = data.get('contact_phone', '')
    status = data['status']
    description = data.get('description', '')
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        cursor.execute(
            "INSERT INTO warehouses (warehouse_code, warehouse_name, location, area, manager, contact_phone, status, description, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (warehouse_code, warehouse_name, location, area, manager, contact_phone, status, description, current_time, current_time)
        )
        conn.commit()
        
        # 获取新添加的仓库
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM warehouses WHERE warehouse_code = ?", (warehouse_code,))
        warehouse = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "仓库添加成功", 
            "data": warehouse
        })
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "仓库编码已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新仓库
@app.route('/api/warehouses/<warehouse_code>', methods=['PUT'])
def update_warehouse(warehouse_code):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查仓库是否存在
    cursor.execute("SELECT * FROM warehouses WHERE warehouse_code = ?", (warehouse_code,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "仓库不存在"}), 404
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'warehouse_name' in data and data['warehouse_name']:
        update_fields.append("warehouse_name = ?")
        params.append(data['warehouse_name'])
    
    if 'location' in data and data['location']:
        update_fields.append("location = ?")
        params.append(data['location'])
    
    if 'area' in data and data['area']:
        update_fields.append("area = ?")
        params.append(float(data['area']))
    
    if 'manager' in data and data['manager']:
        update_fields.append("manager = ?")
        params.append(data['manager'])
    
    if 'contact_phone' in data:
        update_fields.append("contact_phone = ?")
        params.append(data['contact_phone'])
    
    if 'status' in data and data['status']:
        update_fields.append("status = ?")
        params.append(data['status'])
    
    if 'description' in data:
        update_fields.append("description = ?")
        params.append(data['description'])
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    
    # 添加仓库编码到参数列表
    params.append(warehouse_code)
    
    try:
        query = f"UPDATE warehouses SET {', '.join(update_fields)} WHERE warehouse_code = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的仓库
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM warehouses WHERE warehouse_code = ?", (warehouse_code,))
        warehouse = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "仓库信息更新成功", 
            "data": warehouse
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除仓库
@app.route('/api/warehouses/<warehouse_code>', methods=['DELETE'])
def delete_warehouse(warehouse_code):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查仓库是否存在
    cursor.execute("SELECT * FROM warehouses WHERE warehouse_code = ?", (warehouse_code,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "仓库不存在"}), 404
    
    try:
        cursor.execute("DELETE FROM warehouses WHERE warehouse_code = ?", (warehouse_code,))
        conn.commit()
        return jsonify({
            "success": True, 
            "message": "仓库删除成功"
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取入库记录列表
@app.route('/api/stock-in-records', methods=['GET'])
def get_stock_in_records():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取查询参数
    product_code = request.args.get('product_code')
    warehouse_code = request.args.get('warehouse_code')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    # 构建查询条件
    conditions = []
    params = []
    
    if product_code:
        conditions.append("product_code = ?")
        params.append(product_code)
    
    if warehouse_code:
        conditions.append("warehouse_code = ?")
        params.append(warehouse_code)
    
    if start_date:
        conditions.append("entry_date >= ?")
        params.append(start_date)
    
    if end_date:
        conditions.append("entry_date <= ?")
        params.append(end_date)
    
    # 构建SQL查询
    query = "SELECT * FROM stock_in_records"
    if conditions:
        query += " WHERE " + " AND ".join(conditions)
    query += " ORDER BY create_time DESC"
    
    cursor.execute(query, params)
    records = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": records})

# API路由：获取单个入库记录
@app.route('/api/stock-in-records/<record_number>', methods=['GET'])
def get_stock_in_record(record_number):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM stock_in_records WHERE record_number = ?", (record_number,))
    record = cursor.fetchone()
    
    conn.close()
    
    if record:
        return jsonify({"success": True, "data": dict(record)})
    else:
        return jsonify({"success": False, "message": "入库记录不存在"}), 404

# API路由：添加入库记录
@app.route('/api/stock-in-records', methods=['POST'])
def add_stock_in_record():
    data = request.json
    
    # 验证必填字段
    required_fields = ['product_code', 'warehouse_code', 'quantity', 'supplier', 'operator', 'entry_date']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 检查商品是否存在
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (data['product_code'],))
    product = cursor.fetchone()
    
    if not product:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    product = dict(product)
    
    # 检查仓库是否存在
    cursor.execute("SELECT * FROM warehouses WHERE warehouse_code = ?", (data['warehouse_code'],))
    warehouse = cursor.fetchone()
    
    if not warehouse:
        conn.close()
        return jsonify({"success": False, "message": "仓库不存在"}), 404
    
    warehouse = dict(warehouse)
    
    # 新增：统计该商品在该仓库的实际库存
    cursor.execute("""
        SELECT 
            IFNULL((SELECT SUM(quantity) FROM stock_in_records WHERE product_code = ? AND warehouse_code = ?), 0) -
            IFNULL((SELECT SUM(quantity) FROM stock_out_records WHERE product_code = ? AND warehouse_code = ?), 0) AS warehouse_stock
    """, (data['product_code'], data['warehouse_code'], data['product_code'], data['warehouse_code']))
    warehouse_stock = cursor.fetchone()[0]

    quantity = int(data['quantity'])
    if warehouse_stock < quantity:
        conn.close()
        return jsonify({
            "success": False,
            "message": f"该商品在所选仓库库存不足，当前库存: {warehouse_stock}"
        }), 400

    # 检查总库存是否足够（保留原有逻辑，防止数据不一致）
    if product['current_stock'] < quantity:
        conn.close()
        return jsonify({
            "success": False, 
            "message": f"库存不足，当前库存: {product['current_stock']}"
        }), 400
    
    # 生成入库单号
    current_date = datetime.now().strftime("%Y%m%d")
    cursor.execute("SELECT COUNT(*) FROM stock_in_records WHERE record_number LIKE ?", (f"IN{current_date}%",))
    count = cursor.fetchone()[0]
    record_number = f"IN{current_date}{str(count + 1).zfill(4)}"
    
    # 格式化数据
    product_code = data['product_code']
    product_name = product['product_name']
    warehouse_code = data['warehouse_code']
    warehouse_name = warehouse['warehouse_name']
    batch_number = data.get('batch_number', '')
    production_date = data.get('production_date', '')
    expiry_date = data.get('expiry_date', '')
    supplier = data['supplier']
    operator = data['operator']  # 注意：operator字段实际存储的是商品成本，非操作员信息
    note = data.get('note', '')
    status = data.get('status', '已入库')
    entry_date = data['entry_date']
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 添加入库记录
        cursor.execute(
            "INSERT INTO stock_in_records (record_number, product_code, product_name, warehouse_code, warehouse_name, quantity, batch_number, production_date, expiry_date, supplier, operator, note, status, entry_date, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (record_number, product_code, product_name, warehouse_code, warehouse_name, quantity, batch_number, production_date, expiry_date, supplier, operator, note, status, entry_date, current_time, current_time)
        )
        
        # 更新商品库存
        cursor.execute(
            "UPDATE products SET current_stock = current_stock + ?, update_time = ? WHERE product_code = ?",
            (quantity, current_time, product_code)
        )
        
        conn.commit()
        
        # 获取新添加的记录
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM stock_in_records WHERE record_number = ?", (record_number,))
        record = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "入库记录添加成功", 
            "data": record
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新入库记录
@app.route('/api/stock-in-records/<record_number>', methods=['PUT'])
def update_stock_in_record(record_number):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查入库记录是否存在
    cursor.execute("SELECT * FROM stock_in_records WHERE record_number = ?", (record_number,))
    record = cursor.fetchone()
    
    if not record:
        conn.close()
        return jsonify({"success": False, "message": "入库记录不存在"}), 404
    
    record = dict(record)
    old_quantity = record['quantity']
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'batch_number' in data:
        update_fields.append("batch_number = ?")
        params.append(data['batch_number'])
    
    if 'production_date' in data:
        update_fields.append("production_date = ?")
        params.append(data['production_date'])
    
    if 'expiry_date' in data:
        update_fields.append("expiry_date = ?")
        params.append(data['expiry_date'])
    
    if 'note' in data:
        update_fields.append("note = ?")
        params.append(data['note'])
    
    if 'status' in data and data['status']:
        update_fields.append("status = ?")
        params.append(data['status'])
    
    # 如果更新了数量，需要同时更新商品库存
    quantity_change = 0
    if 'quantity' in data and data['quantity'] and int(data['quantity']) != old_quantity:
        # 获取商品当前库存
        cursor.execute("SELECT current_stock FROM products WHERE product_code = ?", (record['product_code'],))
        product = cursor.fetchone()
        current_stock = dict(product)['current_stock']
        
        new_quantity = int(data['quantity'])
        quantity_change = new_quantity - old_quantity
        
        # 检查库存是否足够（如果是增加入库数量）
        if quantity_change > 0 and current_stock < quantity_change:
            conn.close()
            return jsonify({
                "success": False, 
                "message": f"库存不足，当前库存: {current_stock}，不能再入库 {quantity_change} 个"
            }), 400
        
        update_fields.append("quantity = ?")
        params.append(new_quantity)
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    params.append(current_time)
    
    # 添加记录号到参数列表
    params.append(record_number)
    
    try:
        # 更新入库记录
        query = f"UPDATE stock_in_records SET {', '.join(update_fields)} WHERE record_number = ?"
        cursor.execute(query, params)
        
        # 如果数量有变化，更新商品库存
        if quantity_change != 0:
            cursor.execute(
                "UPDATE products SET current_stock = current_stock + ?, update_time = ? WHERE product_code = ?",
                (quantity_change, current_time, record['product_code'])
            )
        
        conn.commit()
        
        # 获取更新后的记录
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM stock_in_records WHERE record_number = ?", (record_number,))
        updated_record = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "入库记录更新成功", 
            "data": updated_record
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除入库记录
@app.route('/api/stock-in-records/<record_number>', methods=['DELETE'])
def delete_stock_in_record(record_number):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 获取商品信息和入库数量
        cursor.execute(
            "SELECT product_code, quantity FROM stock_in_records WHERE record_number = ?",
            (record_number,)
        )
        record = cursor.fetchone()
        
        if not record:
            conn.close()
            return jsonify({
                'success': False,
                'message': '入库记录不存在'
            }), 404
        
        product_code, quantity = record
        
        # 更新商品库存（恢复库存）
        cursor.execute(
            "UPDATE products SET current_stock = current_stock - ? WHERE product_code = ?",
            (quantity, product_code)
        )
        
        # 删除入库记录
        cursor.execute(
            "DELETE FROM stock_in_records WHERE record_number = ?",
            (record_number,)
        )
        
        conn.commit()
        
        return jsonify({
            'success': True,
            'message': '入库记录删除成功'
        })
    except Exception as e:
        conn.rollback()
        return jsonify({
            'success': False,
            'message': f'删除失败：{str(e)}'
        }), 500
    finally:
        conn.close()

# API路由：获取库存预警设置
@app.route('/api/inventory-alerts', methods=['GET'])
def get_inventory_alerts():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 获取所有预警设置
        cursor.execute('''
        SELECT s.*, p.current_stock 
        FROM inventory_alert_settings s
        LEFT JOIN products p ON s.product_code = p.product_code
        ORDER BY 
            CASE 
                WHEN p.current_stock < s.min_stock THEN 1
                WHEN p.current_stock > s.max_stock THEN 2
                ELSE 3
            END
        ''')
        
        alert_settings = cursor.fetchall()
        result = []
        
        for setting in alert_settings:
            current_stock = setting['current_stock'] or 0
            min_stock = setting['min_stock'] or 0
            max_stock = setting['max_stock'] or 0
            
            # 确定库存状态
            if current_stock <= 0:
                status = '缺货'
            elif current_stock < min_stock:
                status = '预警'
            elif current_stock > max_stock:
                status = '积压'
            else:
                status = '正常'
                
            result.append({
                'id': setting['id'],
                'product_code': setting['product_code'],
                'product_name': setting['product_name'],
                'category': setting['category'],
                'current_stock': current_stock,
                'min_stock': min_stock,
                'max_stock': max_stock,
                'alert_enabled': setting['alert_enabled'] == 1,
                'alert_level': setting['alert_level'],
                'status': status,
                'create_time': setting['create_time'],
                'update_time': setting['update_time']
            })
        
        conn.close()
        return jsonify({'success': True, 'data': result})
    
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取库存预警设置失败: {str(e)}'}), 500

# API路由：获取库存统计数据
@app.route('/api/inventory-stats', methods=['GET'])
def get_inventory_stats():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 获取总商品数
        cursor.execute('SELECT COUNT(*) as total FROM products')
        total_products = cursor.fetchone()['total']
        
        # 获取预警商品数
        cursor.execute('''
        SELECT COUNT(*) as warning_count
        FROM products p
        JOIN inventory_alert_settings s ON p.product_code = s.product_code
        WHERE p.current_stock < s.min_stock AND p.current_stock > 0
        ''')
        warning_count = cursor.fetchone()['warning_count']
        
        # 获取缺货商品数
        cursor.execute('''
        SELECT COUNT(*) as out_of_stock_count
        FROM products
        WHERE current_stock <= 0
        ''')
        out_of_stock_count = cursor.fetchone()['out_of_stock_count']
        
        conn.close()
        
        return jsonify({
            'success': True,
            'data': {
                'total_products': total_products,
                'warning_count': warning_count,
                'out_of_stock_count': out_of_stock_count
            }
        })
    
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取库存统计数据失败: {str(e)}'}), 500

# API路由：添加库存预警设置
@app.route('/api/inventory-alerts', methods=['POST'])
def add_inventory_alert():
    try:
        data = request.json
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 检查是否已存在该商品的预警设置
        cursor.execute('SELECT * FROM inventory_alert_settings WHERE product_code = ?', 
                      (data['product_code'],))
        existing = cursor.fetchone()
        
        if existing:
            conn.close()
            return jsonify({'success': False, 'message': '该商品已有预警设置'}), 400
        
        # 检查商品是否存在
        cursor.execute('SELECT * FROM products WHERE product_code = ?', 
                      (data['product_code'],))
        product = cursor.fetchone()
        
        if not product:
            conn.close()
            return jsonify({'success': False, 'message': '商品不存在'}), 404
        
        product = dict(product)
        
        # 添加预警设置
        cursor.execute('''
        INSERT INTO inventory_alert_settings 
        (product_code, product_name, category, min_stock, max_stock, alert_enabled, alert_level, create_time, update_time)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            data['product_code'],
            product['product_name'],
            product['category'],
            data.get('min_stock', 10),
            data.get('max_stock', 100),
            1 if data.get('alert_enabled', True) else 0,
            data.get('alert_level', '中'),
            datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        ))
        
        conn.commit()
        
        # 获取新添加的预警设置ID
        alert_id = cursor.lastrowid
        
        # 获取新添加的预警设置
        cursor.execute('''
        SELECT s.*, p.current_stock
        FROM inventory_alert_settings s
        LEFT JOIN products p ON s.product_code = p.product_code
        WHERE s.id = ?
        ''', (alert_id,))
        
        new_alert = cursor.fetchone()
        
        if new_alert:
            new_alert = dict(new_alert)
            current_stock = new_alert['current_stock'] or 0
            min_stock = new_alert['min_stock'] or 0
            max_stock = new_alert['max_stock'] or 0
            
            # 确定库存状态
            if current_stock <= 0:
                status = '缺货'
            elif current_stock < min_stock:
                status = '预警'
            elif current_stock > max_stock:
                status = '积压'
            else:
                status = '正常'
                
            result = {
                'id': new_alert['id'],
                'product_code': new_alert['product_code'],
                'product_name': new_alert['product_name'],
                'category': new_alert['category'],
                'current_stock': current_stock,
                'min_stock': min_stock,
                'max_stock': max_stock,
                'alert_enabled': new_alert['alert_enabled'] == 1,
                'alert_level': new_alert['alert_level'],
                'status': status,
                'create_time': new_alert['create_time'],
                'update_time': new_alert['update_time']
            }
        else:
            result = None
        
        conn.close()
        
        return jsonify({'success': True, 'message': '库存预警设置添加成功', 'data': result})
    
    except Exception as e:
        return jsonify({'success': False, 'message': f'添加库存预警设置失败: {str(e)}'}), 500

# API路由：更新库存预警设置
@app.route('/api/inventory-alerts/<int:alert_id>', methods=['PUT'])
def update_inventory_alert(alert_id):
    try:
        data = request.json
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 检查预警设置是否存在
        cursor.execute('SELECT * FROM inventory_alert_settings WHERE id = ?', (alert_id,))
        existing = cursor.fetchone()
        
        if not existing:
            conn.close()
            return jsonify({'success': False, 'message': '预警设置不存在'}), 404
        
        existing = dict(existing)
        
        # 更新预警设置
        cursor.execute('''
        UPDATE inventory_alert_settings
        SET min_stock = ?, max_stock = ?, alert_enabled = ?, alert_level = ?, update_time = ?
        WHERE id = ?
        ''', (
            data.get('min_stock', existing['min_stock']),
            data.get('max_stock', existing['max_stock']),
            1 if data.get('alert_enabled', existing['alert_enabled'] == 1) else 0,
            data.get('alert_level', existing['alert_level']),
            datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            alert_id
        ))
        
        conn.commit()
        
        # 获取更新后的预警设置
        cursor.execute('''
        SELECT s.*, p.current_stock
        FROM inventory_alert_settings s
        LEFT JOIN products p ON s.product_code = p.product_code
        WHERE s.id = ?
        ''', (alert_id,))
        
        updated_alert = cursor.fetchone()
        
        if updated_alert:
            updated_alert = dict(updated_alert)
            current_stock = updated_alert['current_stock'] or 0
            min_stock = updated_alert['min_stock'] or 0
            max_stock = updated_alert['max_stock'] or 0
            
            # 确定库存状态
            if current_stock <= 0:
                status = '缺货'
            elif current_stock < min_stock:
                status = '预警'
            elif current_stock > max_stock:
                status = '积压'
            else:
                status = '正常'
                
            result = {
                'id': updated_alert['id'],
                'product_code': updated_alert['product_code'],
                'product_name': updated_alert['product_name'],
                'category': updated_alert['category'],
                'current_stock': current_stock,
                'min_stock': min_stock,
                'max_stock': max_stock,
                'alert_enabled': updated_alert['alert_enabled'] == 1,
                'alert_level': updated_alert['alert_level'],
                'status': status,
                'create_time': updated_alert['create_time'],
                'update_time': updated_alert['update_time']
            }
        else:
            result = None
        
        conn.close()
        
        return jsonify({'success': True, 'message': '库存预警设置更新成功', 'data': result})
    
    except Exception as e:
        return jsonify({'success': False, 'message': f'更新库存预警设置失败: {str(e)}'}), 500

# API路由：删除库存预警设置
@app.route('/api/inventory-alerts/<int:alert_id>', methods=['DELETE'])
def delete_inventory_alert(alert_id):
    try:
        conn = sqlite3.connect(DATABASE)
        cursor = conn.cursor()
        
        # 检查预警设置是否存在
        cursor.execute('SELECT * FROM inventory_alert_settings WHERE id = ?', (alert_id,))
        existing = cursor.fetchone()
        
        if not existing:
            conn.close()
            return jsonify({'success': False, 'message': '预警设置不存在'}), 404
        
        # 删除预警设置
        cursor.execute('DELETE FROM inventory_alert_settings WHERE id = ?', (alert_id,))
        
        conn.commit()
        conn.close()
        
        return jsonify({'success': True, 'message': '库存预警设置删除成功'})
    
    except Exception as e:
        return jsonify({'success': False, 'message': f'删除库存预警设置失败: {str(e)}'}), 500

# API路由：获取所有未设置预警的商品
@app.route('/api/products/without-alerts', methods=['GET'])
def get_products_without_alerts():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        cursor.execute('''
        SELECT p.* FROM products p
        LEFT JOIN inventory_alert_settings s ON p.product_code = s.product_code
        WHERE s.id IS NULL
        ''')
        
        products = cursor.fetchall()
        result = []
        
        for product in products:
            result.append({
                'id': product['id'],
                'product_code': product['product_code'],
                'product_name': product['product_name'],
                'category': product['category'],
                'current_stock': product['current_stock'],
                'price': product['price']
            })
        
        conn.close()
        return jsonify({'success': True, 'data': result})
    
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取商品列表失败: {str(e)}'}), 500

# API路由：获取出库记录列表
@app.route('/api/stock-out-records', methods=['GET'])
def get_stock_out_records():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取查询参数
    product_code = request.args.get('product_code')
    warehouse_code = request.args.get('warehouse_code')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    # 构建查询条件
    conditions = []
    params = []
    
    if product_code:
        conditions.append("(product_code = ? OR product_name LIKE ?)")
        params.append(product_code)
        params.append(f"%{product_code}%")
    
    if warehouse_code:
        conditions.append("warehouse_code = ?")
        params.append(warehouse_code)
    
    if start_date:
        conditions.append("out_date >= ?")
        params.append(start_date)
    
    if end_date:
        conditions.append("out_date <= ?")
        params.append(end_date)
    
    # 构建SQL查询
    query = "SELECT * FROM stock_out_records"
    if conditions:
        query += " WHERE " + " AND ".join(conditions)
    query += " ORDER BY create_time DESC"
    
    cursor.execute(query, params)
    records = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "records": records})

# API路由：获取单个出库记录
@app.route('/api/stock-out-records/<record_number>', methods=['GET'])
def get_stock_out_record(record_number):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM stock_out_records WHERE record_number = ?", (record_number,))
    record = cursor.fetchone()
    
    conn.close()
    
    if record:
        return jsonify({"success": True, "record": dict(record)})
    else:
        return jsonify({"success": False, "message": "出库记录不存在"}), 404

# API路由：添加出库记录
@app.route('/api/stock-out-records', methods=['POST'])
def add_stock_out_record():
    data = request.json
    
    # 验证必填字段
    required_fields = ['product_code', 'warehouse_code', 'quantity', 'out_reason', 'recipient', 'operator', 'out_date']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 检查商品是否存在
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (data['product_code'],))
    product = cursor.fetchone()
    
    if not product:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    product = dict(product)
    
    # 检查仓库是否存在
    cursor.execute("SELECT * FROM warehouses WHERE warehouse_code = ?", (data['warehouse_code'],))
    warehouse = cursor.fetchone()
    
    if not warehouse:
        conn.close()
        return jsonify({"success": False, "message": "仓库不存在"}), 404
    
    warehouse = dict(warehouse)
    
    # 新增：统计该商品在该仓库的实际库存
    cursor.execute("""
        SELECT 
            IFNULL((SELECT SUM(quantity) FROM stock_in_records WHERE product_code = ? AND warehouse_code = ?), 0) -
            IFNULL((SELECT SUM(quantity) FROM stock_out_records WHERE product_code = ? AND warehouse_code = ?), 0) AS warehouse_stock
    """, (data['product_code'], data['warehouse_code'], data['product_code'], data['warehouse_code']))
    warehouse_stock = cursor.fetchone()[0]

    quantity = int(data['quantity'])
    if warehouse_stock < quantity:
        conn.close()
        return jsonify({
            "success": False,
            "message": f"该商品在所选仓库库存不足，当前库存: {warehouse_stock}"
        }), 400

    # 检查总库存是否足够（保留原有逻辑，防止数据不一致）
    if product['current_stock'] < quantity:
        conn.close()
        return jsonify({
            "success": False, 
            "message": f"库存不足，当前库存: {product['current_stock']}"
        }), 400
    
    # 生成出库单号
    current_date = datetime.now().strftime("%Y%m%d")
    cursor.execute("SELECT COUNT(*) FROM stock_out_records WHERE record_number LIKE ?", (f"OUT{current_date}%",))
    count = cursor.fetchone()[0]
    record_number = f"OUT{current_date}{str(count + 1).zfill(4)}"
    
    # 格式化数据
    product_code = data['product_code']
    product_name = product['product_name']
    warehouse_code = data['warehouse_code']
    warehouse_name = warehouse['warehouse_name']
    batch_number = data.get('batch_number', '')
    out_reason = data['out_reason']
    recipient = data['recipient']
    operator = data['operator']
    note = data.get('note', '')
    status = data.get('status', '已出库')
    out_date = data['out_date']
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 添加出库记录
        cursor.execute(
            "INSERT INTO stock_out_records (record_number, product_code, product_name, warehouse_code, warehouse_name, quantity, batch_number, out_reason, recipient, operator, note, status, out_date, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (record_number, product_code, product_name, warehouse_code, warehouse_name, quantity, batch_number, out_reason, recipient, operator, note, status, out_date, current_time, current_time)
        )
        
        # 更新商品库存
        cursor.execute(
            "UPDATE products SET current_stock = current_stock - ? WHERE product_code = ?",
            (quantity, product_code)
        )
        
        conn.commit()
        
        # 获取新添加的记录
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM stock_out_records WHERE record_number = ?", (record_number,))
        record = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "出库记录添加成功", 
            "record": record
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新出库记录
@app.route('/api/stock-out-records/<record_number>', methods=['PUT'])
def update_stock_out_record(record_number):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查出库记录是否存在
    cursor.execute("SELECT * FROM stock_out_records WHERE record_number = ?", (record_number,))
    record = cursor.fetchone()
    
    if not record:
        conn.close()
        return jsonify({"success": False, "message": "出库记录不存在"}), 404
    
    record = dict(record)
    old_quantity = record['quantity']
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    # 新增：支持更新仓库
    if 'warehouse_code' in data and data['warehouse_code']:
        # 查找仓库名
        cursor.execute("SELECT warehouse_name FROM warehouses WHERE warehouse_code = ?", (data['warehouse_code'],))
        warehouse = cursor.fetchone()
        if warehouse:
            update_fields.append("warehouse_code = ?")
            params.append(data['warehouse_code'])
            update_fields.append("warehouse_name = ?")
            params.append(warehouse['warehouse_name'])
        else:
            conn.close()
            return jsonify({"success": False, "message": "仓库不存在"}), 400
    
    if 'batch_number' in data:
        update_fields.append("batch_number = ?")
        params.append(data['batch_number'])
    
    if 'out_reason' in data and data['out_reason']:
        update_fields.append("out_reason = ?")
        params.append(data['out_reason'])
    
    if 'recipient' in data and data['recipient']:
        update_fields.append("recipient = ?")
        params.append(data['recipient'])
    
    if 'note' in data:
        update_fields.append("note = ?")
        params.append(data['note'])
    
    if 'status' in data and data['status']:
        update_fields.append("status = ?")
        params.append(data['status'])
    
    if 'out_date' in data and data['out_date']:
        update_fields.append("out_date = ?")
        params.append(data['out_date'])
    
    # 如果更新了数量，需要同时更新商品库存
    quantity_change = 0
    if 'quantity' in data and data['quantity'] and int(data['quantity']) != old_quantity:
        # 获取商品当前库存
        cursor.execute("SELECT current_stock FROM products WHERE product_code = ?", (record['product_code'],))
        product = cursor.fetchone()
        current_stock = dict(product)['current_stock']
        
        new_quantity = int(data['quantity'])
        quantity_change = new_quantity - old_quantity
        
        # 检查库存是否足够（如果是增加出库数量）
        if quantity_change > 0 and current_stock < quantity_change:
            conn.close()
            return jsonify({
                "success": False, 
                "message": f"库存不足，当前库存: {current_stock}，不能再出库 {quantity_change} 个"
            }), 400
        
        update_fields.append("quantity = ?")
        params.append(new_quantity)
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    params.append(current_time)
    
    # 添加记录号到参数列表
    params.append(record_number)
    
    try:
        # 更新出库记录
        query = f"UPDATE stock_out_records SET {', '.join(update_fields)} WHERE record_number = ?"
        cursor.execute(query, params)
        
        # 如果数量有变化，更新商品库存
        if quantity_change != 0:
            cursor.execute(
                "UPDATE products SET current_stock = current_stock - ?, update_time = ? WHERE product_code = ?",
                (quantity_change, current_time, record['product_code'])
            )
        
        conn.commit()
        
        # 获取更新后的记录
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM stock_out_records WHERE record_number = ?", (record_number,))
        updated_record = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "出库记录更新成功", 
            "record": updated_record
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除出库记录
@app.route('/api/stock-out-records/<record_number>', methods=['DELETE'])
def delete_stock_out_record(record_number):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 获取商品信息和出库数量
        cursor.execute(
            "SELECT product_code, quantity FROM stock_out_records WHERE record_number = ?",
            (record_number,)
        )
        record = cursor.fetchone()
        
        if not record:
            conn.close()
            return jsonify({
                'success': False,
                'message': '出库记录不存在'
            }), 404
        
        product_code, quantity = record
        
        # 更新商品库存（恢复库存）
        cursor.execute(
            "UPDATE products SET current_stock = current_stock + ? WHERE product_code = ?",
            (quantity, product_code)
        )
        
        # 删除出库记录
        cursor.execute(
            "DELETE FROM stock_out_records WHERE record_number = ?",
            (record_number,)
        )
        
        conn.commit()
        
        return jsonify({
            'success': True,
            'message': '出库记录删除成功'
        })
    except Exception as e:
        conn.rollback()
        return jsonify({
            'success': False,
            'message': f'删除失败：{str(e)}'
        }), 500
    finally:
        conn.close()

# 创建销售相关表格
def init_sales_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 创建销售表（记录每笔交易）
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS sales (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sale_number TEXT NOT NULL UNIQUE,
        sale_date TEXT NOT NULL,
        total_amount REAL NOT NULL,
        discount_amount REAL DEFAULT 0,
        final_amount REAL NOT NULL,
        payment_method TEXT NOT NULL,
        cashier TEXT NOT NULL,
        customer_name TEXT,
        customer_phone TEXT,
        status TEXT NOT NULL,
        note TEXT,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建销售商品明细表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS sale_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sale_number TEXT NOT NULL,
        product_code TEXT NOT NULL,
        product_name TEXT NOT NULL,
        category TEXT NOT NULL,
        unit_price REAL NOT NULL,
        quantity INTEGER NOT NULL,
        discount REAL DEFAULT 0,
        subtotal REAL NOT NULL,
        create_time TEXT NOT NULL,
        FOREIGN KEY (sale_number) REFERENCES sales(sale_number) ON DELETE CASCADE
    )
    ''')
    
    conn.commit()
    conn.close()

# 初始化销售数据库
init_sales_db()

# API路由：获取所有销售记录
@app.route('/api/sales', methods=['GET'])
def get_sales():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取查询参数
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    payment_method = request.args.get('payment_method')
    cashier = request.args.get('cashier')
    
    # 构建查询条件
    conditions = []
    params = []
    
    if start_date:
        conditions.append("sale_date >= ?")
        params.append(start_date)
    
    if end_date:
        conditions.append("sale_date <= ?")
        params.append(end_date)
    
    if payment_method:
        conditions.append("payment_method = ?")
        params.append(payment_method)
    
    if cashier:
        conditions.append("cashier = ?")
        params.append(cashier)
    
    # 构建SQL查询
    query = "SELECT * FROM sales"
    if conditions:
        query += " WHERE " + " AND ".join(conditions)
    query += " ORDER BY create_time DESC"
    
    cursor.execute(query, params)
    sales = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": sales})

# API路由：获取单个销售记录及其商品明细
@app.route('/api/sales/<sale_number>', methods=['GET'])
def get_sale(sale_number):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取销售记录
    cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    sale = dict(sale)
    
    # 获取销售商品明细
    cursor.execute("SELECT * FROM sale_items WHERE sale_number = ?", (sale_number,))
    items = [dict(row) for row in cursor.fetchall()]
    
    # 合并结果
    result = {
        "sale": sale,
        "items": items
    }
    
    conn.close()
    return jsonify({"success": True, "data": result})

# API路由：创建新销售记录（开始新交易）
@app.route('/api/sales', methods=['POST'])
def create_sale():
    data = request.json
    
    # 生成销售单号
    current_date = datetime.now().strftime("%Y%m%d")
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    cursor.execute("SELECT COUNT(*) FROM sales WHERE sale_number LIKE ?", (f"SALE{current_date}%",))
    count = cursor.fetchone()[0]
    sale_number = f"SALE{current_date}{str(count + 1).zfill(4)}"
    
    # 格式化数据
    sale_date = data.get('sale_date', datetime.now().strftime("%Y-%m-%d"))
    cashier = data.get('cashier', '')
    payment_method = data.get('payment_method', '现金')
    customer_name = data.get('customer_name', '')
    customer_phone = data.get('customer_phone', '')
    note = data.get('note', '')
    status = data.get('status', '进行中')  # 初始状态为进行中
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 插入基本销售信息（总额初始为0）
        cursor.execute(
            "INSERT INTO sales (sale_number, sale_date, total_amount, discount_amount, final_amount, payment_method, cashier, customer_name, customer_phone, status, note, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (sale_number, sale_date, 0, 0, 0, payment_method, cashier, customer_name, customer_phone, status, note, current_time, current_time)
        )
        
        conn.commit()
        
        # 返回创建的销售记录
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
        sale = dict(cursor.fetchone())
        
        conn.close()
        
        return jsonify({
            "success": True,
            "message": "销售记录创建成功",
            "data": sale
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"创建销售记录失败: {str(e)}"}), 500

# API路由：更新销售记录
@app.route('/api/sales/<sale_number>', methods=['PUT'])
def update_sale(sale_number):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查销售记录是否存在
    cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'payment_method' in data:
        update_fields.append("payment_method = ?")
        params.append(data['payment_method'])
    
    if 'cashier' in data:
        update_fields.append("cashier = ?")
        params.append(data['cashier'])
    
    if 'customer_name' in data:
        update_fields.append("customer_name = ?")
        params.append(data['customer_name'])
    
    if 'customer_phone' in data:
        update_fields.append("customer_phone = ?")
        params.append(data['customer_phone'])
    
    if 'note' in data:
        update_fields.append("note = ?")
        params.append(data['note'])
    
    if 'status' in data:
        update_fields.append("status = ?")
        params.append(data['status'])
    
    if 'discount_amount' in data:
        update_fields.append("discount_amount = ?")
        discount_amount = float(data['discount_amount'])
        params.append(discount_amount)
        
        # 更新最终金额
        cursor.execute("SELECT total_amount FROM sales WHERE sale_number = ?", (sale_number,))
        total_amount = cursor.fetchone()[0]
        final_amount = total_amount - discount_amount
        
        update_fields.append("final_amount = ?")
        params.append(final_amount)
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    
    # 添加销售单号到参数列表
    params.append(sale_number)
    
    try:
        query = f"UPDATE sales SET {', '.join(update_fields)} WHERE sale_number = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的销售记录
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
        updated_sale = dict(cursor.fetchone())
        
        conn.close()
        
        return jsonify({
            "success": True,
            "message": "销售记录更新成功",
            "data": updated_sale
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500

# API路由：删除销售记录
@app.route('/api/sales/<sale_number>', methods=['DELETE'])
def delete_sale(sale_number):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查销售记录是否存在
    cursor.execute("SELECT status FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    status = sale[0]
    
    # 检查是否可以删除（只有未完成的销售可以删除）
    if status == '已完成':
        conn.close()
        return jsonify({"success": False, "message": "已完成的销售记录不能删除"}), 400
    
    try:
        # 先获取销售商品明细
        cursor.execute("SELECT product_code, quantity FROM sale_items WHERE sale_number = ?", (sale_number,))
        items = cursor.fetchall()
        
        # 恢复商品库存
        for product_code, quantity in items:
            cursor.execute(
                "UPDATE products SET current_stock = current_stock + ? WHERE product_code = ?",
                (quantity, product_code)
            )
        
        # 删除销售商品明细
        cursor.execute("DELETE FROM sale_items WHERE sale_number = ?", (sale_number,))
        
        # 删除销售记录
        cursor.execute("DELETE FROM sales WHERE sale_number = ?", (sale_number,))
        
        conn.commit()
        conn.close()
        
        return jsonify({
            "success": True,
            "message": "销售记录删除成功"
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500

# API路由：添加商品到销售记录
@app.route('/api/sales/<sale_number>/items', methods=['POST'])
def add_sale_item(sale_number):
    data = request.json
    
    # 验证必填字段
    required_fields = ['product_code', 'quantity']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查销售记录是否存在
    cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    sale = dict(sale)
    
    # 检查销售状态
    if sale['status'] == '已完成':
        conn.close()
        return jsonify({"success": False, "message": "销售已完成，不能添加商品"}), 400
    
    # 检查商品是否存在
    product_code = data['product_code']
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
    product = cursor.fetchone()
    
    if not product:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    product = dict(product)
    
    # 检查库存是否足够
    quantity = int(data['quantity'])
    if product['current_stock'] < quantity:
        conn.close()
        return jsonify({
            "success": False,
            "message": f"库存不足，当前库存: {product['current_stock']}"
        }), 400
    
    # 计算商品小计
    unit_price = product['price']
    discount = float(data.get('discount', 0))
    subtotal = unit_price * quantity - discount
    
    # 获取当前时间
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 添加销售商品明细
        cursor.execute(
            "INSERT INTO sale_items (sale_number, product_code, product_name, category, unit_price, quantity, discount, subtotal, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (sale_number, product_code, product['product_name'], product['category'], unit_price, quantity, discount, subtotal, current_time)
        )
        
        # 更新商品库存
        cursor.execute(
            "UPDATE products SET current_stock = current_stock - ?, update_time = ? WHERE product_code = ?",
            (quantity, current_time, product_code)
        )
        
        # 更新销售总额
        cursor.execute(
            "SELECT SUM(subtotal) FROM sale_items WHERE sale_number = ?",
            (sale_number,)
        )
        total_amount = cursor.fetchone()[0]
        
        # 获取折扣金额
        cursor.execute(
            "SELECT discount_amount FROM sales WHERE sale_number = ?",
            (sale_number,)
        )
        discount_amount = cursor.fetchone()[0]
        
        # 计算最终金额
        final_amount = total_amount - discount_amount
        
        # 更新销售记录
        cursor.execute(
            "UPDATE sales SET total_amount = ?, final_amount = ?, update_time = ? WHERE sale_number = ?",
            (total_amount, final_amount, current_time, sale_number)
        )
        
        conn.commit()
        
        # 获取添加的商品明细
        cursor.execute("SELECT * FROM sale_items WHERE sale_number = ? ORDER BY id DESC LIMIT 1", (sale_number,))
        item = dict(cursor.fetchone())
        
        # 获取更新后的销售记录
        cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
        updated_sale = dict(cursor.fetchone())
        
        conn.close()
        
        return jsonify({
            "success": True,
            "message": "商品添加成功",
            "data": {
                "item": item,
                "sale": updated_sale
            }
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"添加商品失败: {str(e)}"}), 500

# API路由：获取销售记录的所有商品
@app.route('/api/sales/<sale_number>/items', methods=['GET'])
def get_sale_items(sale_number):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查销售记录是否存在
    cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    # 获取销售商品明细
    cursor.execute("SELECT * FROM sale_items WHERE sale_number = ?", (sale_number,))
    items = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    
    return jsonify({
        "success": True,
        "data": items
    })

# API路由：从销售记录中移除商品
@app.route('/api/sales/<sale_number>/items/<int:item_id>', methods=['DELETE'])
def remove_sale_item(sale_number, item_id):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查销售记录是否存在
    cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    sale = dict(sale)
    
    # 检查销售状态
    if sale['status'] == '已完成':
        conn.close()
        return jsonify({"success": False, "message": "销售已完成，不能移除商品"}), 400
    
    # 检查商品是否存在
    cursor.execute("SELECT * FROM sale_items WHERE id = ? AND sale_number = ?", (item_id, sale_number))
    item = cursor.fetchone()
    
    if not item:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在或不属于该销售记录"}), 404
    
    item = dict(item)
    product_code = item['product_code']
    quantity = item['quantity']
    subtotal = item['subtotal']
    
    try:
        # 恢复商品库存
        cursor.execute(
            "UPDATE products SET current_stock = current_stock + ? WHERE product_code = ?",
            (quantity, product_code)
        )
        
        # 删除销售商品明细
        cursor.execute("DELETE FROM sale_items WHERE id = ?", (item_id,))
        
        # 更新销售总额
        cursor.execute(
            "SELECT SUM(subtotal) FROM sale_items WHERE sale_number = ?",
            (sale_number,)
        )
        result = cursor.fetchone()[0]
        total_amount = result if result else 0
        
        # 获取折扣金额
        discount_amount = sale['discount_amount']
        
        # 计算最终金额
        final_amount = total_amount - discount_amount
        
        # 更新销售记录
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute(
            "UPDATE sales SET total_amount = ?, final_amount = ?, update_time = ? WHERE sale_number = ?",
            (total_amount, final_amount, current_time, sale_number)
        )
        
        conn.commit()
        
        # 获取更新后的销售记录
        cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
        updated_sale = dict(cursor.fetchone())
        
        conn.close()
        
        return jsonify({
            "success": True,
            "message": "商品移除成功",
            "data": updated_sale
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"移除商品失败: {str(e)}"}), 500

# API路由：更新销售商品数量
@app.route('/api/sales/<sale_number>/items/<int:item_id>', methods=['PUT'])
def update_sale_item(sale_number, item_id):
    data = request.json
    
    # 验证必填字段
    if 'quantity' not in data or not data['quantity']:
        return jsonify({"success": False, "message": "请提供商品数量"}), 400
    
    new_quantity = int(data['quantity'])
    if new_quantity <= 0:
        return jsonify({"success": False, "message": "商品数量必须大于0"}), 400
    
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查销售记录是否存在
    cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    sale = dict(sale)
    
    # 检查销售状态
    if sale['status'] == '已完成':
        conn.close()
        return jsonify({"success": False, "message": "销售已完成，不能修改商品"}), 400
    
    # 检查商品是否存在
    cursor.execute("SELECT * FROM sale_items WHERE id = ? AND sale_number = ?", (item_id, sale_number))
    item = cursor.fetchone()
    
    if not item:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在或不属于该销售记录"}), 404
    
    item = dict(item)
    product_code = item['product_code']
    old_quantity = item['quantity']
    unit_price = item['unit_price']
    discount = item['discount']
    
    # 计算数量变化
    quantity_change = new_quantity - old_quantity
    
    # 如果数量增加，检查库存是否足够
    if quantity_change > 0:
        cursor.execute("SELECT current_stock FROM products WHERE product_code = ?", (product_code,))
        current_stock = cursor.fetchone()[0]
        
        if current_stock < quantity_change:
            conn.close()
            return jsonify({
                "success": False,
                "message": f"库存不足，当前库存: {current_stock}"
            }), 400
    
    # 计算新的小计
    new_subtotal = unit_price * new_quantity - discount
    
    try:
        # 更新商品库存
        cursor.execute(
            "UPDATE products SET current_stock = current_stock - ? WHERE product_code = ?",
            (quantity_change, product_code)
        )
        
        # 更新销售商品明细
        cursor.execute(
            "UPDATE sale_items SET quantity = ?, subtotal = ? WHERE id = ?",
            (new_quantity, new_subtotal, item_id)
        )
        
        # 更新销售总额
        cursor.execute(
            "SELECT SUM(subtotal) FROM sale_items WHERE sale_number = ?",
            (sale_number,)
        )
        total_amount = cursor.fetchone()[0]
        
        # 获取折扣金额
        discount_amount = sale['discount_amount']
        
        # 计算最终金额
        final_amount = total_amount - discount_amount
        
        # 更新销售记录
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute(
            "UPDATE sales SET total_amount = ?, final_amount = ?, update_time = ? WHERE sale_number = ?",
            (total_amount, final_amount, current_time, sale_number)
        )
        
        conn.commit()
        
        # 获取更新后的商品明细
        cursor.execute("SELECT * FROM sale_items WHERE id = ?", (item_id,))
        updated_item = dict(cursor.fetchone())
        
        # 获取更新后的销售记录
        cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
        updated_sale = dict(cursor.fetchone())
        
        conn.close()
        
        return jsonify({
            "success": True,
            "message": "商品数量更新成功",
            "data": {
                "item": updated_item,
                "sale": updated_sale
            }
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"更新商品数量失败: {str(e)}"}), 500

# API路由：完成销售（结账）
@app.route('/api/sales/<sale_number>/checkout', methods=['POST'])
def checkout_sale(sale_number):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查销售记录是否存在
    cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
    sale = cursor.fetchone()
    
    if not sale:
        conn.close()
        return jsonify({"success": False, "message": "销售记录不存在"}), 404
    
    sale = dict(sale)
    
    # 检查销售状态
    if sale['status'] == '已完成':
        conn.close()
        return jsonify({"success": False, "message": "销售已完成，不能重复结账"}), 400
    
    # 检查是否有商品
    cursor.execute("SELECT COUNT(*) FROM sale_items WHERE sale_number = ?", (sale_number,))
    item_count = cursor.fetchone()[0]
    
    if item_count == 0:
        conn.close()
        return jsonify({"success": False, "message": "销售记录没有商品，不能结账"}), 400
    
    # 更新销售记录信息
    payment_method = data.get('payment_method', sale['payment_method'])
    cashier = data.get('cashier', sale['cashier'])
    customer_name = data.get('customer_name', sale['customer_name'])
    customer_phone = data.get('customer_phone', sale['customer_phone'])
    note = data.get('note', sale['note'])
    
    # 获取购物车中的商品
    cursor.execute("""
        SELECT si.*, p.category 
        FROM sale_items si
        JOIN products p ON si.product_code = p.product_code
        WHERE si.sale_number = ?
    """, (sale_number,))
    items = [dict(row) for row in cursor.fetchall()]
    
    # 计算原始总金额
    total_amount = sum(item['subtotal'] for item in items)
    
    # 自动计算优惠金额 - 查找适用的促销活动
    discount_amount = 0
    current_datetime = datetime.now()
    current_date = current_datetime.strftime("%Y/%m/%d")
    current_time = current_datetime.strftime("%H:%M")
    
    # 查询当前可用的促销活动
    cursor.execute("""
        SELECT * FROM promotions 
        WHERE status = '进行中'
        AND start_date <= ? AND end_date >= ?
        AND start_time <= ? AND end_time >= ?
    """, (current_date, current_date, current_time, current_time))
    
    active_promotions = [dict(row) for row in cursor.fetchall()]
    
    # 计算每个商品的优惠金额
    for item in items:
        for promo in active_promotions:
            # 检查商品分类是否符合促销条件
            if promo['category'] == '全部分类' or promo['category'] == item['category']:
                # 计算折扣金额（promo.discount 为小数，如 0.8 表示 8 折）
                item_discount = item['subtotal'] * (1 - promo['discount'])
                discount_amount += item_discount
                break
    
    # 如果用户手动提供了折扣金额，则使用用户提供的值
    if 'discount_amount' in data:
        user_discount = float(data['discount_amount'])
        # 取较大的折扣值
        discount_amount = max(discount_amount, user_discount)
    
    # 确保折扣金额不超过总金额
    discount_amount = min(discount_amount, total_amount)
    
    # 计算最终金额
    final_amount = total_amount - discount_amount
    
    # 如果最终金额为负数，则返回错误
    if final_amount < 0:
        conn.close()
        return jsonify({"success": False, "message": "折扣金额不能大于销售总额"}), 400
    
    current_time_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 更新销售记录
        cursor.execute(
            "UPDATE sales SET payment_method = ?, cashier = ?, customer_name = ?, customer_phone = ?, note = ?, discount_amount = ?, final_amount = ?, status = ?, update_time = ? WHERE sale_number = ?",
            (payment_method, cashier, customer_name, customer_phone, note, discount_amount, final_amount, '已完成', current_time_str, sale_number)
        )
        
        # 会员积分处理
        member = None
        if customer_phone:
            # 查询会员信息
            cursor.execute("SELECT * FROM members WHERE phone = ?", (customer_phone,))
            member_result = cursor.fetchone()
            if member_result:
                member = dict(member_result)
                # 读取积分累计规则
                rule = get_point_rule()
                rate = rule.get('rate', 1)
                # 计算积分 (每消费1元积rate分)
                earned_points = int(final_amount * float(rate))
                if earned_points > 0:
                    # 添加积分记录
                    cursor.execute(
                        "INSERT INTO point_records (member_code, record_type, points, description, sale_number, record_date, create_time) VALUES (?, ?, ?, ?, ?, ?, ?)",
                        (member['member_code'], 'earn', earned_points, f"购物消费积分 - {sale_number}", sale_number, datetime.now().strftime("%Y-%m-%d"), current_time_str)
                    )
                    # 更新会员积分
                    cursor.execute(
                        "UPDATE members SET points = points + ?, update_time = ? WHERE member_code = ?",
                        (earned_points, current_time_str, member['member_code'])
                    )
                    # 获取更新后的会员信息
                    cursor.execute("SELECT * FROM members WHERE member_code = ?", (member['member_code'],))
                    member = dict(cursor.fetchone())
        
        conn.commit()
        
        # 获取更新后的销售记录
        cursor.execute("SELECT * FROM sales WHERE sale_number = ?", (sale_number,))
        updated_sale = dict(cursor.fetchone())
        
        # 获取销售商品明细
        cursor.execute("SELECT * FROM sale_items WHERE sale_number = ?", (sale_number,))
        items = [dict(row) for row in cursor.fetchall()]
        
        result = {
            "sale": updated_sale,
            "items": items,
        }
        
        # 如果是会员，添加会员信息到返回结果
        if member:
            result["member"] = member
            result["earned_points"] = earned_points if 'earned_points' in locals() else 0
        
        conn.close()
        
        return jsonify({
            "success": True,
            "message": "销售结账完成",
            "data": result
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"结账失败: {str(e)}"}), 500

# API路由：获取销售统计数据
@app.route('/api/sales/stats', methods=['GET'])
def get_sales_stats():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 获取查询参数
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conditions = ["status = '已完成'"]
        params = []
        
        if start_date:
            conditions.append("sale_date >= ?")
            params.append(start_date)
        
        if end_date:
            conditions.append("sale_date <= ?")
            params.append(end_date)
        
        # 构建查询条件字符串
        condition_str = " WHERE " + " AND ".join(conditions)
        
        # 获取总销售额
        query = f"SELECT SUM(final_amount) as total_sales FROM sales{condition_str}"
        cursor.execute(query, params)
        total_sales = cursor.fetchone()[0] or 0
        
        # 获取总销售订单数
        query = f"SELECT COUNT(*) as order_count FROM sales{condition_str}"
        cursor.execute(query, params)
        order_count = cursor.fetchone()[0] or 0
        
        # 获取销售商品总数
        query = f"""
        SELECT SUM(si.quantity) as total_items
        FROM sale_items si
        JOIN sales s ON si.sale_number = s.sale_number
        WHERE s.status = '已完成'
        """
        
        # 添加额外的日期条件
        if start_date:
            query += " AND s.sale_date >= ?"
        if end_date:
            query += " AND s.sale_date <= ?"
            
        cursor.execute(query, params)
        total_items = cursor.fetchone()[0] or 0
        
        # 获取平均订单金额
        avg_order_amount = total_sales / order_count if order_count > 0 else 0
        
        # 按支付方式统计
        query = f"""
        SELECT payment_method, COUNT(*) as count, SUM(final_amount) as amount
        FROM sales
        WHERE status = '已完成'
        """
        
        # 添加额外的日期条件
        if start_date:
            query += " AND sale_date >= ?"
        if end_date:
            query += " AND sale_date <= ?"
            
        query += " GROUP BY payment_method"
        
        cursor.execute(query, params)
        payment_stats = [dict(row) for row in cursor.fetchall()]
        
        # 按日期统计
        query = f"""
        SELECT sale_date, COUNT(*) as count, SUM(final_amount) as amount
        FROM sales
        WHERE status = '已完成'
        """
        
        # 添加额外的日期条件
        if start_date:
            query += " AND sale_date >= ?"
        if end_date:
            query += " AND sale_date <= ?"
            
        query += " GROUP BY sale_date ORDER BY sale_date"
        
        cursor.execute(query, params)
        daily_stats = [dict(row) for row in cursor.fetchall()]
        
        # 按商品分类统计
        query = f"""
        SELECT si.category, SUM(si.quantity) as quantity, SUM(si.subtotal) as amount
        FROM sale_items si
        JOIN sales s ON si.sale_number = s.sale_number
        WHERE s.status = '已完成'
        """
        
        # 添加额外的日期条件
        if start_date:
            query += " AND s.sale_date >= ?"
        if end_date:
            query += " AND s.sale_date <= ?"
            
        query += " GROUP BY si.category ORDER BY amount DESC"
        
        cursor.execute(query, params)
        category_stats = [dict(row) for row in cursor.fetchall()]
        
        conn.close()
        
        return jsonify({
            "success": True,
            "data": {
                "total_sales": total_sales,
                "order_count": order_count,
                "total_items": total_items,
                "avg_order_amount": avg_order_amount,
                "payment_stats": payment_stats,
                "daily_stats": daily_stats,
                "category_stats": category_stats
            }
        })
    
    except Exception as e:
        return jsonify({"success": False, "message": f"获取销售统计数据失败: {str(e)}"}), 500

# API路由：获取热门商品
@app.route('/api/sales/popular-products', methods=['GET'])
def get_popular_products():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 获取查询参数
        limit = request.args.get('limit', 10, type=int)
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conditions = ["s.status = '已完成'"]
        params = []
        
        if start_date:
            conditions.append("s.sale_date >= ?")
            params.append(start_date)
        
        if end_date:
            conditions.append("s.sale_date <= ?")
            params.append(end_date)
        
        # 构建查询条件字符串
        condition_str = " WHERE " + " AND ".join(conditions)
        
        # 获取热门商品
        query = f"""
        SELECT si.product_code, si.product_name, si.category,
               SUM(si.quantity) as total_quantity,
               SUM(si.subtotal) as total_amount,
               COUNT(DISTINCT si.sale_number) as sale_count
        FROM sale_items si
        JOIN sales s ON si.sale_number = s.sale_number
        {condition_str}
        GROUP BY si.product_code
        ORDER BY total_quantity DESC
        LIMIT ?
        """
        params.append(limit)
        
        cursor.execute(query, params)
        popular_products = [dict(row) for row in cursor.fetchall()]
        
        conn.close()
        
        return jsonify({
            "success": True,
            "data": popular_products
        })
    
    except Exception as e:
        return jsonify({"success": False, "message": f"获取热门商品失败: {str(e)}"}), 500

# 创建价格管理相关的表
def init_price_management_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 创建价格策略表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS price_strategies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        strategy_name TEXT NOT NULL UNIQUE,
        strategy_type TEXT NOT NULL,
        discount_rate REAL,
        start_date TEXT,
        end_date TEXT,
        description TEXT,
        status TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建商品价格历史表（扩展现有的price_adjustments表）
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS price_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_code TEXT NOT NULL,
        product_name TEXT NOT NULL,
        price REAL NOT NULL,
        effective_date TEXT NOT NULL,
        end_date TEXT,
        is_current INTEGER DEFAULT 0,
        create_time TEXT NOT NULL
    )
    ''')
    
    # 创建批量价格调整记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS batch_price_adjustments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        batch_number TEXT NOT NULL UNIQUE,
        adjustment_date TEXT NOT NULL,
        category TEXT,
        adjustment_type TEXT NOT NULL,
        adjustment_value REAL NOT NULL,
        reason TEXT,
        operator TEXT NOT NULL,
        affected_products INTEGER DEFAULT 0,
        status TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 添加一些初始价格策略
    strategies = [
        ('正常价格', '固定价格', 0, None, None, '商品的标准零售价格', '启用'),
        ('会员价格', '折扣率', 0.9, None, None, '会员专享9折价格', '启用'),
        ('节假日促销', '折扣率', 0.8, None, None, '节假日促销价格', '停用')
    ]
    
    for strategy in strategies:
        try:
            current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cursor.execute(
                "INSERT INTO price_strategies (strategy_name, strategy_type, discount_rate, start_date, end_date, description, status, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (strategy[0], strategy[1], strategy[2], strategy[3], strategy[4], strategy[5], strategy[6], current_time, current_time)
            )
        except sqlite3.IntegrityError:
            # 如果策略已存在则跳过
            pass
    
    conn.commit()
    conn.close()

# 初始化价格管理数据库
init_price_management_db()

# API路由：获取所有价格策略
@app.route('/api/price-strategies', methods=['GET'])
def get_price_strategies():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM price_strategies ORDER BY create_time DESC")
    strategies = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": strategies})

# API路由：获取单个价格策略
@app.route('/api/price-strategies/<int:strategy_id>', methods=['GET'])
def get_price_strategy(strategy_id):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM price_strategies WHERE id = ?", (strategy_id,))
    strategy = cursor.fetchone()
    
    conn.close()
    
    if strategy:
        return jsonify({"success": True, "data": dict(strategy)})
    else:
        return jsonify({"success": False, "message": "价格策略不存在"}), 404

# API路由：添加价格策略
@app.route('/api/price-strategies', methods=['POST'])
def add_price_strategy():
    data = request.json
    
    # 验证必填字段
    required_fields = ['strategy_name', 'strategy_type', 'status']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 格式化数据
    strategy_name = data['strategy_name']
    strategy_type = data['strategy_type']
    discount_rate = data.get('discount_rate', 0)
    start_date = data.get('start_date')
    end_date = data.get('end_date')
    description = data.get('description', '')
    status = data['status']
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        cursor.execute(
            "INSERT INTO price_strategies (strategy_name, strategy_type, discount_rate, start_date, end_date, description, status, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (strategy_name, strategy_type, discount_rate, start_date, end_date, description, status, current_time, current_time)
        )
        conn.commit()
        
        # 获取新添加的价格策略
        strategy_id = cursor.lastrowid
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM price_strategies WHERE id = ?", (strategy_id,))
        strategy = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "价格策略添加成功", 
            "data": strategy
        })
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "价格策略名称已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：更新价格策略
@app.route('/api/price-strategies/<int:strategy_id>', methods=['PUT'])
def update_price_strategy(strategy_id):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查价格策略是否存在
    cursor.execute("SELECT * FROM price_strategies WHERE id = ?", (strategy_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "价格策略不存在"}), 404
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'strategy_name' in data and data['strategy_name']:
        update_fields.append("strategy_name = ?")
        params.append(data['strategy_name'])
    
    if 'strategy_type' in data and data['strategy_type']:
        update_fields.append("strategy_type = ?")
        params.append(data['strategy_type'])
    
    if 'discount_rate' in data:
        update_fields.append("discount_rate = ?")
        params.append(data['discount_rate'])
    
    if 'start_date' in data:
        update_fields.append("start_date = ?")
        params.append(data['start_date'])
    
    if 'end_date' in data:
        update_fields.append("end_date = ?")
        params.append(data['end_date'])
    
    if 'description' in data:
        update_fields.append("description = ?")
        params.append(data['description'])
    
    if 'status' in data:
        update_fields.append("status = ?")
        params.append(data['status'])
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    
    # 添加策略ID到参数列表
    params.append(strategy_id)
    
    try:
        query = f"UPDATE price_strategies SET {', '.join(update_fields)} WHERE id = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的价格策略
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM price_strategies WHERE id = ?", (strategy_id,))
        strategy = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "价格策略更新成功", 
            "data": strategy
        })
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({"success": False, "message": "价格策略名称已存在"}), 400
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除价格策略
@app.route('/api/price-strategies/<int:strategy_id>', methods=['DELETE'])
def delete_price_strategy(strategy_id):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查价格策略是否存在
    cursor.execute("SELECT * FROM price_strategies WHERE id = ?", (strategy_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "价格策略不存在"}), 404
    
    try:
        cursor.execute("DELETE FROM price_strategies WHERE id = ?", (strategy_id,))
        conn.commit()
        return jsonify({
            "success": True, 
            "message": "价格策略删除成功"
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取价格历史记录
@app.route('/api/price-history', methods=['GET'])
def get_price_history():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取查询参数
    product_code = request.args.get('product_code')
    
    if product_code:
        cursor.execute("SELECT * FROM price_history WHERE product_code = ? ORDER BY effective_date DESC", (product_code,))
    else:
        cursor.execute("SELECT * FROM price_history ORDER BY effective_date DESC")
    
    history = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": history})

# API路由：获取商品当前价格
@app.route('/api/products/<product_code>/price', methods=['GET'])
def get_product_current_price(product_code):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取商品信息
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
    product = cursor.fetchone()
    
    if not product:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    product = dict(product)
    
    # 获取价格历史
    cursor.execute("SELECT * FROM price_history WHERE product_code = ? AND is_current = 1", (product_code,))
    current_price_history = cursor.fetchone()
    
    # 获取价格调整记录
    cursor.execute("SELECT * FROM price_adjustments WHERE product_code = ? ORDER BY adjustment_date DESC LIMIT 5", (product_code,))
    adjustments = [dict(row) for row in cursor.fetchall()]
    
    result = {
        "product_code": product['product_code'],
        "product_name": product['product_name'],
        "current_price": product['price'],
        "price_history": current_price_history and dict(current_price_history) or None,
        "recent_adjustments": adjustments
    }
    
    conn.close()
    return jsonify({"success": True, "data": result})

# API路由：更新商品价格
@app.route('/api/products/<product_code>/price', methods=['PUT'])
def update_product_price(product_code):
    data = request.json
    
    # 验证必填字段
    required_fields = ['new_price', 'operator', 'reason']
    for field in required_fields:
        if field not in data or data[field] == '':
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 检查商品是否存在
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
    product = cursor.fetchone()
    
    if not product:
        conn.close()
        return jsonify({"success": False, "message": "商品不存在"}), 404
    
    product = dict(product)
    
    # 格式化数据
    new_price = float(data['new_price'])
    old_price = product['price']
    reason = data['reason']
    operator = data['operator']
    adjustment_date = datetime.now().strftime("%Y-%m-%d")
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 如果价格没有变化，则返回错误
        if old_price == new_price:
            conn.close()
            return jsonify({"success": False, "message": "新价格与当前价格相同"}), 400
        
        # 添加价格调整记录
        cursor.execute(
            "INSERT INTO price_adjustments (product_code, product_name, old_price, new_price, adjustment_date, reason, operator, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (product_code, product['product_name'], old_price, new_price, adjustment_date, reason, operator, current_time)
        )
        
        # 更新商品表中的价格
        cursor.execute(
            "UPDATE products SET price = ?, update_time = ? WHERE product_code = ?",
            (new_price, current_time, product_code)
        )
        
        # 更新价格历史表
        # 先将所有当前价格记录设为非当前
        cursor.execute(
            "UPDATE price_history SET is_current = 0, end_date = ? WHERE product_code = ? AND is_current = 1",
            (adjustment_date, product_code)
        )
        
        # 添加新的当前价格记录
        cursor.execute(
            "INSERT INTO price_history (product_code, product_name, price, effective_date, is_current, create_time) VALUES (?, ?, ?, ?, ?, ?)",
            (product_code, product['product_name'], new_price, adjustment_date, 1, current_time)
        )
        
        conn.commit()
        
        # 获取新添加的记录
        adjustment_id = cursor.lastrowid
        cursor.execute("SELECT * FROM price_adjustments WHERE id = ?", (adjustment_id,))
        adjustment = dict(cursor.fetchone())
        
        conn.close()
        
        return jsonify({
            "success": True, 
            "message": "价格更新成功", 
            "data": adjustment
        })
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"价格更新失败: {str(e)}"}), 500

# API路由：更新价格调整记录
@app.route('/api/price-adjustments/<int:adjustment_id>', methods=['PUT'])
def update_price_adjustment(adjustment_id):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 检查价格调整记录是否存在
    cursor.execute("SELECT * FROM price_adjustments WHERE id = ?", (adjustment_id,))
    adjustment = cursor.fetchone()
    
    if not adjustment:
        conn.close()
        return jsonify({"success": False, "message": "价格调整记录不存在"}), 404
    
    adjustment = dict(adjustment)
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'reason' in data:
        update_fields.append("reason = ?")
        params.append(data['reason'])
    
    if 'operator' in data and data['operator']:
        update_fields.append("operator = ?")
        params.append(data['operator'])
    
    # 不允许修改价格和商品信息，只能修改原因和操作员
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加记录ID到参数列表
    params.append(adjustment_id)
    
    try:
        query = f"UPDATE price_adjustments SET {', '.join(update_fields)} WHERE id = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的记录
        cursor.execute("SELECT * FROM price_adjustments WHERE id = ?", (adjustment_id,))
        updated_adjustment = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "价格调整记录更新成功", 
            "data": updated_adjustment
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：批量调整价格
@app.route('/api/batch-price-adjustments', methods=['POST'])
def batch_adjust_prices():
    data = request.json
    
    # 验证必填字段
    required_fields = ['adjustment_type', 'adjustment_value', 'operator', 'reason']
    for field in required_fields:
        if field not in data:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 格式化数据
    adjustment_type = data['adjustment_type']  # 可以是 'percentage' 或 'fixed'
    adjustment_value = float(data['adjustment_value'])
    operator = data['operator']
    reason = data['reason']
    category = data.get('category')  # 可选，指定要调整的商品分类
    
    if adjustment_type not in ['percentage', 'fixed']:
        return jsonify({"success": False, "message": "调整类型必须是 'percentage' 或 'fixed'"}), 400
    
    # 生成批量调整编号
    current_date = datetime.now().strftime("%Y%m%d")
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    cursor.execute("SELECT COUNT(*) FROM batch_price_adjustments WHERE batch_number LIKE ?", (f"BATCH{current_date}%",))
    count = cursor.fetchone()[0]
    batch_number = f"BATCH{current_date}{str(count + 1).zfill(4)}"
    
    adjustment_date = datetime.now().strftime("%Y-%m-%d")
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        # 查询需要调整的商品
        if category:
            cursor.execute("SELECT * FROM products WHERE category = ?", (category,))
        else:
            cursor.execute("SELECT * FROM products")
        
        products = cursor.fetchall()
        
        if not products:
            conn.close()
            return jsonify({"success": False, "message": "没有找到符合条件的商品"}), 404
        
        # 创建批量调整记录
        cursor.execute(
            "INSERT INTO batch_price_adjustments (batch_number, adjustment_date, category, adjustment_type, adjustment_value, reason, operator, affected_products, status, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (batch_number, adjustment_date, category, adjustment_type, adjustment_value, reason, operator, len(products), '处理中', current_time, current_time)
        )
        
        # 逐个调整商品价格
        for product in products:
            product_code = product[1]  # product_code
            product_name = product[2]  # product_name
            old_price = product[4]     # price
            
            # 计算新价格
            if adjustment_type == 'percentage':
                new_price = round(old_price * (1 + adjustment_value / 100), 2)
            else:  # fixed
                new_price = round(old_price + adjustment_value, 2)
            
            # 确保价格不低于0
            if new_price < 0:
                new_price = 0
            
            # 添加价格调整记录
            cursor.execute(
                "INSERT INTO price_adjustments (product_code, product_name, old_price, new_price, adjustment_date, reason, operator, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                (product_code, product_name, old_price, new_price, adjustment_date, f"批量调整#{batch_number}: {reason}", operator, current_time)
            )
            
            # 更新商品表中的价格
            cursor.execute(
                "UPDATE products SET price = ?, update_time = ? WHERE product_code = ?",
                (new_price, current_time, product_code)
            )
            
            # 更新价格历史表
            cursor.execute(
                "UPDATE price_history SET is_current = 0, end_date = ? WHERE product_code = ? AND is_current = 1",
                (adjustment_date, product_code)
            )
            
            cursor.execute(
                "INSERT INTO price_history (product_code, product_name, price, effective_date, is_current, create_time) VALUES (?, ?, ?, ?, ?, ?)",
                (product_code, product_name, new_price, adjustment_date, 1, current_time)
            )
        
        # 更新批量调整记录状态
        cursor.execute(
            "UPDATE batch_price_adjustments SET status = ?, update_time = ? WHERE batch_number = ?",
            ('已完成', current_time, batch_number)
        )
        
        conn.commit()
        
        # 获取批量调整记录
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM batch_price_adjustments WHERE batch_number = ?", (batch_number,))
        batch_adjustment = dict(cursor.fetchone())
        
        return jsonify({
            "success": True,
            "message": f"批量调整价格成功，共调整了 {len(products)} 个商品",
            "data": batch_adjustment
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"批量调整价格失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取所有批量价格调整记录
@app.route('/api/batch-price-adjustments', methods=['GET'])
def get_batch_price_adjustments():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM batch_price_adjustments ORDER BY create_time DESC")
    batch_adjustments = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": batch_adjustments})

# API路由：获取单个批量价格调整记录
@app.route('/api/batch-price-adjustments/<batch_number>', methods=['GET'])
def get_batch_price_adjustment(batch_number):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取批量调整记录
    cursor.execute("SELECT * FROM batch_price_adjustments WHERE batch_number = ?", (batch_number,))
    batch_adjustment = cursor.fetchone()
    
    if not batch_adjustment:
        conn.close()
        return jsonify({"success": False, "message": "批量价格调整记录不存在"}), 404
    
    batch_adjustment = dict(batch_adjustment)
    
    # 获取相关的价格调整记录
    cursor.execute("SELECT * FROM price_adjustments WHERE reason LIKE ?", (f"批量调整#{batch_number}:%",))
    adjustments = [dict(row) for row in cursor.fetchall()]
    
    result = {
        "batch_adjustment": batch_adjustment,
        "adjustments": adjustments
    }
    
    conn.close()
    return jsonify({"success": True, "data": result})

# API路由：获取价格统计信息
@app.route('/api/price-stats', methods=['GET'])
def get_price_stats():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取查询参数
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    conditions = []
    params = []
    
    if start_date:
        conditions.append("adjustment_date >= ?")
        params.append(start_date)
    
    if end_date:
        conditions.append("adjustment_date <= ?")
        params.append(end_date)
    
    condition_str = " WHERE " + " AND ".join(conditions) if conditions else ""
    
    # 获取价格调整次数
    query = f"SELECT COUNT(*) as count FROM price_adjustments{condition_str}"
    cursor.execute(query, params)
    adjustment_count = cursor.fetchone()['count']
    
    # 获取平均调整幅度
    query = f"SELECT AVG((new_price - old_price) / old_price * 100) as avg_change FROM price_adjustments{condition_str}"
    cursor.execute(query, params)
    avg_change = cursor.fetchone()['avg_change'] or 0
    
    # 获取涨价次数
    query = f"SELECT COUNT(*) as count FROM price_adjustments WHERE new_price > old_price{' AND ' + ' AND '.join(conditions) if conditions else ''}"
    cursor.execute(query, params)
    increase_count = cursor.fetchone()['count']
    
    # 获取降价次数
    query = f"SELECT COUNT(*) as count FROM price_adjustments WHERE new_price < old_price{' AND ' + ' AND '.join(conditions) if conditions else ''}"
    cursor.execute(query, params)
    decrease_count = cursor.fetchone()['count']
    
    # 按分类统计价格调整
    query = f"""
    SELECT p.category, COUNT(pa.id) as count, AVG((pa.new_price - pa.old_price) / pa.old_price * 100) as avg_change
    FROM price_adjustments pa
    JOIN products p ON pa.product_code = p.product_code
    {condition_str}
    GROUP BY p.category
    """
    cursor.execute(query, params)
    category_stats = [dict(row) for row in cursor.fetchall()]
    
    # 获取调价最频繁的商品
    query = f"""
    SELECT product_code, product_name, COUNT(*) as count
    FROM price_adjustments
    {condition_str}
    GROUP BY product_code
    ORDER BY count DESC
    LIMIT 10
    """
    cursor.execute(query, params)
    frequent_products = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    
    return jsonify({
        "success": True,
        "data": {
            "adjustment_count": adjustment_count,
            "avg_change": round(avg_change, 2),
            "increase_count": increase_count,
            "decrease_count": decrease_count,
            "category_stats": category_stats,
            "frequent_products": frequent_products
        }
    })

# 创建会员相关表格
def init_member_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS members (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        member_code TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        gender INTEGER NOT NULL,  -- 0: 女, 1: 男
        phone TEXT NOT NULL UNIQUE,
        points INTEGER DEFAULT 0,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建积分记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS point_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        member_code TEXT NOT NULL,
        record_type TEXT NOT NULL,
        points INTEGER NOT NULL,
        description TEXT,
        sale_number TEXT,
        record_date TEXT NOT NULL,
        create_time TEXT NOT NULL,
        FOREIGN KEY (member_code) REFERENCES members(member_code)
    )
    ''')
    
    # 添加一些测试数据
    try:
        # 检查是否已经有会员数据
        cursor.execute("SELECT COUNT(*) FROM members")
        count = cursor.fetchone()[0]
        
        if count == 0:
            print("初始化会员测试数据...")
            # 添加一些测试会员
            import datetime
            now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            test_members = [
                ('M20240101001', '张三', 1, '13812345678', 100, now, now),
                ('M20240101002', '李四', 1, '13898765432', 200, now, now),
                ('M20240101003', '王五', 0, '13712345678', 300, now, now),
            ]
            cursor.executemany(
                "INSERT INTO members (member_code, name, gender, phone, points, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?)",
                test_members
            )
            conn.commit()
            print(f"添加了{len(test_members)}条测试会员数据")
    except Exception as e:
        print(f"初始化会员测试数据失败: {e}")
        
    conn.commit()
    conn.close()

# 更新会员表结构
def update_members_table():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 检查是否已存在gender字段
        cursor.execute("PRAGMA table_info(members)")
        columns = cursor.fetchall()
        column_names = [col[1] for col in columns]
        
        # 如果不存在gender字段，添加它
        if 'gender' not in column_names:
            cursor.execute("ALTER TABLE members ADD COLUMN gender TEXT DEFAULT '男'")
            conn.commit()
            print("会员表结构已更新：添加 gender 字段")
        # 如果不存在registration_date字段，添加它并默认当前时间
        if 'registration_date' not in column_names:
            cursor.execute("ALTER TABLE members ADD COLUMN registration_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP")
            conn.commit()
            print("会员表结构已更新：添加 registration_date 字段")
    except Exception as e:
        print(f"更新会员表结构时出错: {str(e)}")
    finally:
        conn.close()

# 初始化会员数据库
init_member_db()
# 更新会员表结构
update_members_table()

# API路由：添加会员
@app.route('/api/members', methods=['POST'])
def add_member():
    data = request.json
    
    # 验证必填字段
    required_fields = ['name', 'phone', 'gender']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    # 格式化数据
    name = data['name']
    phone = data['phone']
    gender = data['gender']
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    # 生成会员编码 (M + 手机号后8位 + 随机4位数)
    import random
    phone_part = phone[-8:] if len(phone) >= 8 else phone
    random_part = str(random.randint(1000, 9999))
    member_code = f"M{phone_part}{random_part}"
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 检查手机号是否已存在
        cursor.execute("SELECT COUNT(*) FROM members WHERE phone = ?", (phone,))
        if cursor.fetchone()[0] > 0:
            conn.close()
            return jsonify({"success": False, "message": "该手机号已注册为会员"}), 400
        
        # 插入会员记录
        cursor.execute(
            "INSERT INTO members (member_code, name, gender, phone, points, create_time, update_time, registration_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (member_code, name, gender, phone, 0, current_time, current_time, current_time)
        )
        
        conn.commit()
        
        # 获取新添加的会员信息
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
        member = dict(cursor.fetchone())
        
        return jsonify({
            "success": True,
            "message": "会员添加成功",
            "data": member
        })
        
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"添加会员失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取会员列表
@app.route('/api/members', methods=['GET'])
def get_members():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取查询参数
    search = request.args.get('search', '')
    
    if search:
        cursor.execute(
            "SELECT * FROM members WHERE member_code LIKE ? OR name LIKE ? OR phone LIKE ? ORDER BY create_time DESC",
            (f"%{search}%", f"%{search}%", f"%{search}%")
        )
    else:
        cursor.execute("SELECT * FROM members ORDER BY create_time DESC")
    
    members = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify({"success": True, "data": members})

# API路由：通过手机号获取会员
@app.route('/api/members/phone/<phone>', methods=['GET'])
def get_member_by_phone(phone):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM members WHERE phone = ?", (phone,))
    member = cursor.fetchone()
    
    conn.close()
    
    if member:
        return jsonify({"success": True, "data": dict(member)})
    else:
        return jsonify({"success": False, "message": "会员不存在"}), 404

# API路由：更新会员信息
@app.route('/api/members/<member_code>', methods=['PUT'])
def update_member(member_code):
    data = request.json
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查会员是否存在
    cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "会员不存在"}), 404
    
    # 设置要更新的字段
    update_fields = []
    params = []
    
    if 'name' in data and data['name']:
        update_fields.append("name = ?")
        params.append(data['name'])
    
    if 'gender' in data and data['gender']:
        update_fields.append("gender = ?")
        params.append(data['gender'])
    
    if 'phone' in data and data['phone']:
        # 检查手机号是否已被其他会员使用
        cursor.execute("SELECT * FROM members WHERE phone = ? AND member_code != ?", (data['phone'], member_code))
        if cursor.fetchone():
            conn.close()
            return jsonify({"success": False, "message": "该手机号已被其他会员使用"}), 400
        
        update_fields.append("phone = ?")
        params.append(data['phone'])
    
    if 'points' in data:
        update_fields.append("points = ?")
        params.append(data['points'])
    
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    
    # 添加更新时间
    update_fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    
    # 添加会员编码到参数列表
    params.append(member_code)
    
    try:
        query = f"UPDATE members SET {', '.join(update_fields)} WHERE member_code = ?"
        cursor.execute(query, params)
        conn.commit()
        
        # 获取更新后的会员信息
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
        member = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": "会员信息更新成功", 
            "data": member
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：删除会员
@app.route('/api/members/<member_code>', methods=['DELETE'])
def delete_member(member_code):
    print(f"收到删除会员请求，会员编码: {member_code}")
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 检查会员是否存在
        cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
        if not cursor.fetchone():
            print(f"会员不存在: {member_code}")
            conn.close()
            return jsonify({"success": False, "message": "会员不存在"}), 404
        
        # 开始删除
        print(f"开始删除会员: {member_code}")
        
        # 删除相关积分记录
        cursor.execute("DELETE FROM point_records WHERE member_code = ?", (member_code,))
        print(f"已删除积分记录")
        
        # 删除会员
        cursor.execute("DELETE FROM members WHERE member_code = ?", (member_code,))
        print(f"已删除会员记录")
        
        conn.commit()
        print(f"删除会员成功: {member_code}")
        
        return jsonify({
            "success": True, 
            "message": "会员删除成功"
        })
    except Exception as e:
        conn.rollback()
        print(f"删除会员失败: {str(e)}")
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()

# 创建财务报表表
def init_financial_reports_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 创建财务报表表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS financial_reports (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sale_number TEXT NOT NULL UNIQUE,
        sale_date TEXT NOT NULL,
        total_amount REAL NOT NULL,
        discount_amount REAL NOT NULL,
        final_amount REAL NOT NULL,
        payment_method TEXT NOT NULL,
        cashier TEXT NOT NULL,
        customer_name TEXT,
        customer_phone TEXT,
        report_date TEXT NOT NULL,
        create_time TEXT NOT NULL
    )''')
    conn.commit()
    conn.close()

# 初始化财务报表数据库
init_financial_reports_db()

# API路由：获取销售记录列表
@app.route('/api/sales/records', methods=['GET'])
def get_sales_records():
    try:
        # 获取查询参数
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        payment_method = request.args.get('payment_method')  # 支付方式过滤
        page = request.args.get('page', 1, type=int)  # 页码，默认第1页
        page_size = request.args.get('page_size', 10, type=int)  # 每页数量，默认10条
        
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 构建查询条件
        conditions = ["status = '已完成'"]
        params = []
        
        if start_date:
            conditions.append("sale_date >= ?")
            params.append(start_date)
        
        if end_date:
            conditions.append("sale_date <= ?")
            params.append(end_date)
        if payment_method:
            conditions.append("payment_method = ?")
            params.append(payment_method)
        
        # 构建查询条件字符串
        condition_str = " WHERE " + " AND ".join(conditions)
        
        # 获取符合条件的总记录数
        count_query = f"SELECT COUNT(*) as total FROM sales{condition_str}"
        cursor.execute(count_query, params)
        total = cursor.fetchone()['total']
        
        # 计算总页数
        total_pages = (total + page_size - 1) // page_size
        
        # 计算偏移量
        offset = (page - 1) * page_size
        
        # 获取销售记录（分页）
        query = f"""
        SELECT sale_number, sale_date, total_amount, discount_amount, final_amount, 
               payment_method, cashier, customer_name, customer_phone
        FROM sales
        {condition_str}
        ORDER BY sale_date DESC, id DESC
        LIMIT ? OFFSET ?
        """
        cursor.execute(query, params + [page_size, offset])
        sales = [dict(row) for row in cursor.fetchall()]
        
        # 获取销售记录明细
        result = []
        for sale in sales:
            # 获取商品明细
            cursor.execute("""
            SELECT product_code, product_name, category, unit_price, quantity, discount, subtotal
            FROM sale_items
            WHERE sale_number = ?
            """, (sale['sale_number'],))
            
            items = [dict(row) for row in cursor.fetchall()]
            
            # 添加到结果
            sale_with_items = sale.copy()
            sale_with_items['items'] = items
            result.append(sale_with_items)
        
        conn.close()
        
        return jsonify({
            "success": True,
            "data": result,
            "pagination": {
                "total": total,
                "page": page,
                "page_size": page_size,
                "total_pages": total_pages
            }
        })
    
    except Exception as e:
        return jsonify({"success": False, "message": f"获取销售记录失败: {str(e)}"}), 500

# API路由：获取月度销售数据
@app.route('/api/sales/monthly-stats', methods=['GET'])
def get_monthly_sales_stats():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 获取查询参数
        year = request.args.get('year')
        
        # 如果没有提供年份，默认使用当前年份
        if not year:
            year = datetime.now().strftime("%Y")
        
        # 查询每月销售数据
        query = """
        SELECT 
            strftime('%Y-%m', sale_date) as month,
            COUNT(*) as order_count,
            SUM(final_amount) as total_amount,
            SUM(total_amount) as original_amount,
            SUM(discount_amount) as discount_amount,
            AVG(final_amount) as avg_order_amount
        FROM sales
        WHERE status = '已完成' AND strftime('%Y', sale_date) = ?
        GROUP BY strftime('%Y-%m', sale_date)
        ORDER BY month
        """
        
        cursor.execute(query, (year,))
        monthly_stats = [dict(row) for row in cursor.fetchall()]
        
        # 查询每月销售的商品数量
        query = """
        SELECT 
            strftime('%Y-%m', s.sale_date) as month,
            SUM(si.quantity) as item_count,
            COUNT(DISTINCT si.product_code) as product_count
        FROM sale_items si
        JOIN sales s ON si.sale_number = s.sale_number
        WHERE s.status = '已完成' AND strftime('%Y', s.sale_date) = ?
        GROUP BY strftime('%Y-%m', s.sale_date)
        ORDER BY month
        """
        
        cursor.execute(query, (year,))
        monthly_item_stats = [dict(row) for row in cursor.fetchall()]
        
        # 合并结果
        result = []
        for stat in monthly_stats:
            month = stat['month']
            # 查找对应月份的商品统计数据
            item_stat = next((item for item in monthly_item_stats if item['month'] == month), None)
            
            month_data = {
                'month': month,
                'order_count': stat['order_count'],
                'total_amount': stat['total_amount'],
                'original_amount': stat['original_amount'],
                'discount_amount': stat['discount_amount'],
                'avg_order_amount': stat['avg_order_amount'],
                'item_count': item_stat['item_count'] if item_stat else 0,
                'product_count': item_stat['product_count'] if item_stat else 0
            }
            result.append(month_data)
        
        conn.close()
        
        return jsonify({
            "success": True,
            "data": result
        })
    
    except Exception as e:
        return jsonify({"success": False, "message": f"获取月度销售数据失败: {str(e)}"}), 500

@app.route('/api/inventory-products', methods=['GET'])
def get_inventory_products():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        # 联合查询所有商品及其预警设置
        cursor.execute('''
            SELECT p.product_code, p.product_name, p.category, p.current_stock, s.min_stock
            FROM products p
            LEFT JOIN inventory_alert_settings s ON p.product_code = s.product_code
            ORDER BY p.create_time DESC
        ''')
        rows = cursor.fetchall()
        result = []
        for row in rows:
            product_code = row['product_code']
            product_name = row['product_name']
            category = row['category']
            current_stock = row['current_stock']
            min_stock = row['min_stock'] if row['min_stock'] is not None else None
            # 状态判断
            if min_stock is not None:
                if current_stock <= 0:
                    status = '缺货'
                elif current_stock < min_stock:
                    status = '预警'
                else:
                    status = '正常'
            else:
                if current_stock <= 0:
                    status = '缺货'
                else:
                    status = '正常'
            result.append({
                'product_code': product_code,
                'product_name': product_name,
                'category': category,
                'current_stock': current_stock,
                'min_stock': min_stock,
                'status': status
            })
        conn.close()
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取库存商品信息失败: {str(e)}'}), 500

# API路由：获取会员积分记录
@app.route('/api/members/<member_code>/points', methods=['GET'])
def get_member_points(member_code):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 获取会员信息
    cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
    member = cursor.fetchone()
    
    if not member:
        conn.close()
        return jsonify({"success": False, "message": "会员不存在"}), 404
    
    # 获取会员积分记录
    cursor.execute(
        "SELECT * FROM point_records WHERE member_code = ? ORDER BY create_time DESC",
        (member_code,)
    )
    records = [dict(row) for row in cursor.fetchall()]
    
    result = {
        "member": dict(member),
        "records": records
    }
    
    conn.close()
    return jsonify({"success": True, "data": result})

# API路由：添加积分记录
@app.route('/api/members/<member_code>/points', methods=['POST'])
def add_points(member_code):
    data = request.json
    
    # 验证必填字段
    required_fields = ['points', 'record_type', 'description']
    for field in required_fields:
        if field not in data:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    points = int(data['points'])
    record_type = data['record_type']  # 'earn' 或 'use'
    description = data['description']
    sale_number = data.get('sale_number', '')
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 检查会员是否存在
    cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
    member = cursor.fetchone()
    
    if not member:
        conn.close()
        return jsonify({"success": False, "message": "会员不存在"}), 404
    
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    record_date = datetime.now().strftime("%Y-%m-%d")
    
    try:
        # 添加积分记录
        cursor.execute(
            "INSERT INTO point_records (member_code, record_type, points, description, sale_number, record_date, create_time) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (member_code, record_type, points, description, sale_number, record_date, current_time)
        )
        
        # 更新会员积分
        if record_type == 'earn':
            cursor.execute(
                "UPDATE members SET points = points + ?, update_time = ? WHERE member_code = ?",
                (points, current_time, member_code)
            )
        else:  # 'use'
            cursor.execute(
                "UPDATE members SET points = points - ?, update_time = ? WHERE member_code = ?",
                (points, current_time, member_code)
            )
        
        conn.commit()
        
        # 获取更新后的会员信息
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
        updated_member = dict(cursor.fetchone())
        
        return jsonify({
            "success": True, 
            "message": f"积分{'增加' if record_type == 'earn' else '使用'}成功",
            "data": updated_member
        })
        
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"积分操作失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：获取单个会员
@app.route('/api/members/<member_code>', methods=['GET'])
def get_member(member_code):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
    member = cursor.fetchone()
    
    conn.close()
    
    if member:
        return jsonify({"success": True, "data": dict(member)})
    else:
        return jsonify({"success": False, "message": "会员不存在"}), 404

# API路由：获取可兑换商品列表
@app.route('/api/products/exchange', methods=['GET'])
def get_exchange_products():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # 从redeem_settings表中获取已配置的兑换商品信息
    cursor.execute("""
        SELECT p.product_code, p.product_name, p.category, p.current_stock, p.price,
               r.required_points
        FROM products p
        LEFT JOIN redeem_settings r ON p.product_code = r.product_code
        WHERE p.current_stock > 0
        ORDER BY p.product_name
    """)
    products = []
    for row in cursor.fetchall():
        product = dict(row)
        # 如果商品没有配置兑换积分，默认使用价格 * 10
        if product['required_points'] is None:
            product['required_points'] = int(product['price'] * 10)
        products.append(product)
    
    conn.close()
    return jsonify({"success": True, "data": products})

# API路由：更新商品兑换积分设置
@app.route('/api/products/exchange/<product_code>', methods=['PUT'])
def update_exchange_points(product_code):
    data = request.json
    if 'required_points' not in data:
        return jsonify({"success": False, "message": "缺少required_points参数"}), 400
    
    required_points = int(data['required_points'])
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 检查商品是否存在
        cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
        if not cursor.fetchone():
            conn.close()
            return jsonify({"success": False, "message": "商品不存在"}), 404
        
        # 更新或插入兑换积分设置
        cursor.execute("""
            INSERT OR REPLACE INTO redeem_settings 
            (product_code, required_points, create_time) 
            VALUES (?, ?, ?)
        """, (product_code, required_points, current_time))
        
        conn.commit()
        
        # 获取更新后的设置
        cursor.execute("""
            SELECT p.product_code, p.product_name, p.category, p.current_stock, p.price,
                   r.required_points
            FROM products p
            LEFT JOIN redeem_settings r ON p.product_code = r.product_code
            WHERE p.product_code = ?
        """, (product_code,))
        
        product = dict(cursor.fetchone())
        
        return jsonify({
            "success": True,
            "message": "兑换积分设置已更新",
            "data": product
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

# API路由：会员积分兑换
@app.route('/api/redeem', methods=['POST'])
def redeem_points():
    data = request.json
    
    # 验证必填字段
    required_fields = ['member_code', 'product_code', 'quantity']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400
    
    member_code = data['member_code']
    product_code = data['product_code']
    quantity = int(data['quantity'])
    points_cost = data.get('points_cost')  # 可能由前端传入自定义积分消耗
    
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    try:
        # 检查会员是否存在
        cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
        member = cursor.fetchone()
        
        if not member:
            conn.close()
            return jsonify({"success": False, "message": "会员不存在"}), 404
        
        member = dict(member)
        
        # 检查商品是否存在
        cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
        product = cursor.fetchone()
        
        if not product:
            conn.close()
            return jsonify({"success": False, "message": "商品不存在"}), 404
        
        product = dict(product)
        
        # 检查库存是否足够
        if product['current_stock'] < quantity:
            conn.close()
            return jsonify({
                "success": False, 
                "message": f"库存不足，当前库存: {product['current_stock']}"
            }), 400
        
        # 如果未指定积分消耗，则按照商品价格计算（每元10积分）
        if not points_cost:
            points_cost = int(product['price'] * 10 * quantity)
        
        # 检查积分是否足够
        if member['points'] < points_cost:
            conn.close()
            return jsonify({
                "success": False, 
                "message": f"积分不足，当前积分: {member['points']}, 需要积分: {points_cost}"
            }), 400
        
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        record_date = datetime.now().strftime("%Y-%m-%d")
        
        # 添加积分记录
        description = f"兑换商品 {product['product_name']} x {quantity}"
        cursor.execute(
            "INSERT INTO point_records (member_code, record_type, points, description, record_date, create_time) VALUES (?, ?, ?, ?, ?, ?)",
            (member_code, 'use', points_cost, description, record_date, current_time)
        )
        
        # 更新会员积分
        cursor.execute(
            "UPDATE members SET points = points - ?, update_time = ? WHERE member_code = ?",
            (points_cost, current_time, member_code)
        )
        
        # 更新商品库存
        cursor.execute(
            "UPDATE products SET current_stock = current_stock - ?, update_time = ? WHERE product_code = ?",
            (quantity, current_time, product_code)
        )
        
        conn.commit()
        
        # 获取更新后的会员信息
        cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
        updated_member = dict(cursor.fetchone())
        
        # 获取更新后的商品信息
        cursor.execute("SELECT * FROM products WHERE product_code = ?", (product_code,))
        updated_product = dict(cursor.fetchone())
        
        # 获取新添加的积分记录
        cursor.execute("SELECT * FROM point_records ORDER BY id DESC LIMIT 1")
        record = dict(cursor.fetchone())
        
        # 获取最近的积分记录
        cursor.execute(
            """SELECT pr.*, m.name as member_name 
               FROM point_records pr
               JOIN members m ON pr.member_code = m.member_code
               WHERE pr.member_code = ?
               ORDER BY pr.create_time DESC LIMIT 10""", 
            (member_code,)
        )
        recent_records = [dict(row) for row in cursor.fetchall()]
        
        conn.close()
        
        return jsonify({
            "success": True, 
            "message": "兑换成功",
            "data": {
                "member": updated_member,
                "product": updated_product,
                "record": record,
                "recent_records": recent_records
            }
        })
    
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"兑换失败: {str(e)}"}), 500

# API路由：获取会员积分记录
@app.route('/api/members/<member_code>/point-records', methods=['GET'])
def get_member_point_records(member_code):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    try:
        # 检查会员是否存在
        cursor.execute("SELECT * FROM members WHERE member_code = ?", (member_code,))
        member = cursor.fetchone()
        
        if not member:
            conn.close()
            return jsonify({"success": False, "message": "会员不存在"}), 404
        
        # 获取积分记录
        cursor.execute(
            """SELECT pr.*, m.name as member_name 
               FROM point_records pr
               JOIN members m ON pr.member_code = m.member_code
               WHERE pr.member_code = ?
               ORDER BY pr.create_time DESC""", 
            (member_code,)
        )
        records = [dict(row) for row in cursor.fetchall()]
        
        conn.close()
        
        return jsonify({
            "success": True,
            "data": records
        })
    
    except Exception as e:
        conn.close()
        return jsonify({"success": False, "message": f"获取积分记录失败: {str(e)}"}), 500

# API路由：获取最近的积分记录（不指定特定会员）
@app.route('/api/point-records/recent', methods=['GET'])
def get_recent_point_records():
    try:
        conn = sqlite3.connect(DATABASE)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        # 获取查询参数
        limit = request.args.get('limit', 10, type=int)
        
        # 获取最近的积分记录
        cursor.execute('''
            SELECT pr.*, m.name as member_name, 
                  (CASE pr.record_type WHEN 'earn' THEN '+' ELSE '-' END || pr.points) as points_change
            FROM point_records pr
            JOIN members m ON pr.member_code = m.member_code
            ORDER BY pr.create_time DESC
            LIMIT ?
        ''', (limit,))
        
        records = []
        for row in cursor.fetchall():
            record = dict(row)
            
            # 获取此记录时会员的积分余额
            cursor.execute('''
                SELECT points 
                FROM members 
                WHERE member_code = ?
            ''', (record['member_code'],))
            
            member_points = cursor.fetchone()
            current_points = member_points['points'] if member_points else 0
            
            # 如果是获取记录，需要减去积分，如果是使用记录，需要加上积分
            if record['record_type'] == 'earn':
                record['remaining_points'] = current_points - record['points']
            else:
                record['remaining_points'] = current_points + record['points']
                
            records.append(record)
        
        # 获取积分统计信息
        # 1. 总发放积分
        cursor.execute('''
            SELECT SUM(points) as total
            FROM point_records
            WHERE record_type = 'earn'
        ''')
        total_earned = cursor.fetchone()['total'] or 0
        
        # 2. 今日发放积分
        today = datetime.now().strftime("%Y-%m-%d")
        cursor.execute('''
            SELECT SUM(points) as total
            FROM point_records
            WHERE record_type = 'earn' AND record_date = ?
        ''', (today,))
        today_earned = cursor.fetchone()['total'] or 0
        
        # 3. 今日使用积分
        cursor.execute('''
            SELECT SUM(points) as total
            FROM point_records
            WHERE record_type = 'use' AND record_date = ?
        ''', (today,))
        today_used = cursor.fetchone()['total'] or 0
        
        statistics = {
            'total_earned': total_earned,
            'today_earned': today_earned,
            'today_used': today_used
        }
        
        conn.close()
        
        return jsonify({
            'success': True,
            'data': records,
            'statistics': statistics
        })
        
    except Exception as e:
        if conn:
            conn.close()
        return jsonify({
            'success': False,
            'message': f'获取积分记录失败: {str(e)}'
        }), 500

# 更新会员表结构
def update_members_table():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    try:
        # 检查是否已存在gender字段
        cursor.execute("PRAGMA table_info(members)")
        columns = cursor.fetchall()
        column_names = [col[1] for col in columns]
        
        # 如果不存在gender字段，添加它
        if 'gender' not in column_names:
            cursor.execute("ALTER TABLE members ADD COLUMN gender TEXT DEFAULT '男'")
            conn.commit()
            print("会员表结构已更新：添加 gender 字段")
        # 如果不存在registration_date字段，添加它并默认当前时间
        if 'registration_date' not in column_names:
            cursor.execute("ALTER TABLE members ADD COLUMN registration_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP")
            conn.commit()
            print("会员表结构已更新：添加 registration_date 字段")
    except Exception as e:
        print(f"更新会员表结构时出错: {str(e)}")
    finally:
        conn.close()

# 初始化会员数据库
def init_member_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS members (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        member_code TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        gender INTEGER NOT NULL,  -- 0: 女, 1: 男
        phone TEXT NOT NULL UNIQUE,
        points INTEGER DEFAULT 0,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL
    )
    ''')
    
    # 创建积分记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS point_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        member_code TEXT NOT NULL,
        record_type TEXT NOT NULL,
        points INTEGER NOT NULL,
        description TEXT,
        sale_number TEXT,
        record_date TEXT NOT NULL,
        create_time TEXT NOT NULL,
        FOREIGN KEY (member_code) REFERENCES members(member_code)
    )
    ''')
    
    # 添加一些测试数据
    try:
        # 检查是否已经有会员数据
        cursor.execute("SELECT COUNT(*) FROM members")
        count = cursor.fetchone()[0]
        
        if count == 0:
            print("初始化会员测试数据...")
            # 添加一些测试会员
            import datetime
            now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            test_members = [
                ('M20240101001', '张三', 1, '13812345678', 100, now, now),
                ('M20240101002', '李四', 1, '13898765432', 200, now, now),
                ('M20240101003', '王五', 0, '13712345678', 300, now, now),
            ]
            cursor.executemany(
                "INSERT INTO members (member_code, name, gender, phone, points, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?)",
                test_members
            )
            conn.commit()
            print(f"添加了{len(test_members)}条测试会员数据")
    except Exception as e:
        print(f"初始化会员测试数据失败: {e}")
        
    conn.commit()
    conn.close()

# 初始化会员数据库
init_member_db()
# 更新会员表结构
update_members_table()

# 创建兑换商品设置表
import sqlite3
from datetime import datetime

def init_redeem_settings_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS redeem_settings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_code TEXT NOT NULL UNIQUE,
        required_points INTEGER DEFAULT 0,
        create_time TEXT NOT NULL
    )
    ''')
    # 确保 required_points 列存在（旧版本升级）
    cursor.execute("PRAGMA table_info(redeem_settings)")
    cols = [row[1] for row in cursor.fetchall()]
    if 'required_points' not in cols:
        cursor.execute("ALTER TABLE redeem_settings ADD COLUMN required_points INTEGER DEFAULT 0")
    conn.commit()
    conn.close()

# 初始化兑换商品配置表
init_redeem_settings_db()

# API路由：获取兑换商品列表（从持久化配置中读取）
@app.route('/api/exchange-products', methods=['GET'])
def get_exchange_products_settings():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('''
        SELECT r.product_code, r.required_points, p.product_name, p.category, p.price, p.current_stock
        FROM redeem_settings r
        JOIN products p ON r.product_code = p.product_code
        WHERE p.current_stock > 0
        ORDER BY p.product_name
    ''')
    products = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return jsonify({"success": True, "data": products})

# API路由：添加兑换商品（持久化）
@app.route('/api/exchange-products', methods=['POST'])
def add_exchange_products():
    data = request.json
    codes = data.get('product_codes') if isinstance(data.get('product_codes'), list) else []
    points = data.get('required_points') if isinstance(data.get('required_points'), list) else []
    if not codes:
        return jsonify({"success": False, "message": "缺少 product_codes 列表"}), 400
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    added = 0
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    for idx, code in enumerate(codes):
        req_points = int(points[idx]) if idx < len(points) and points[idx] is not None else 0
        try:
            cursor.execute(
                "INSERT OR REPLACE INTO redeem_settings (product_code, required_points, create_time) VALUES (?, ?, ?)",
                (code, req_points, now)
            )
            added += 1
        except sqlite3.IntegrityError:
            continue
    conn.commit()
    conn.close()
    return jsonify({"success": True, "message": f"成功添加{added}个兑换商品"})

# API路由：修改兑换商品的所需积分
@app.route('/api/exchange-products/<product_code>', methods=['PUT'])
def update_exchange_product_points(product_code):
    data = request.json
    required_points = data.get('required_points')
    if required_points is None:
        return jsonify({"success": False, "message": "缺少 required_points"}), 400
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("UPDATE redeem_settings SET required_points = ? WHERE product_code = ?", (required_points, product_code))
    conn.commit()
    conn.close()
    return jsonify({"success": True, "message": "所需积分已更新"})

# 添加员工信息管理API路由
@app.route('/api/employees', methods=['GET'])
def get_employees():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM employees ORDER BY create_time DESC")
    employees = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return jsonify({"success": True, "data": employees})

@app.route('/api/employees/<employee_code>', methods=['GET'])
def get_employee(employee_code):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM employees WHERE employee_code = ?", (employee_code,))
    employee = cursor.fetchone()
    conn.close()
    if employee:
        return jsonify({"success": True, "data": dict(employee)})
    else:
        return jsonify({"success": False, "message": "员工不存在"}), 404

@app.route('/api/employees', methods=['POST'])
def add_employee():
    data = request.json
    required_fields = ['name', 'gender', 'department', 'position', 'hire_date', 'phone']
    for field in required_fields:
        if field not in data or not data[field]:
            return jsonify({"success": False, "message": f"缺少必填字段：{field}"}), 400

    # 部门缩写映射
    dept_map = {'财务部': 'FIN', '采购部': 'PUR', '仓储部': 'WAR'}
    dept = data['department']
    if dept not in dept_map:
        return jsonify({"success": False, "message": "无效的部门"}), 400
    dept_abbrev = dept_map[dept]

    hire_date = data['hire_date']  # YYYY-MM-DD
    date_str = hire_date.replace('-', '')

    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    # 查询同部门同日期的员工数量
    cursor.execute("SELECT COUNT(*) FROM employees WHERE department = ? AND hire_date = ?", (dept, hire_date))
    count = cursor.fetchone()[0] or 0
    seq = count + 1
    employee_code = f"{dept_abbrev}{date_str}{seq:03d}"

    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    try:
        cursor.execute(
            "INSERT INTO employees (employee_code, name, gender, department, position, hire_date, phone, email, emergency_contact, work_experience, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (employee_code, data['name'], data['gender'], dept, data['position'], hire_date, data['phone'], data.get('email', ''), data.get('emergency_contact', ''), data.get('work_experience', ''), current_time, current_time)
        )
        conn.commit()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM employees WHERE employee_code = ?", (employee_code,))
        new_employee = dict(cursor.fetchone())
        conn.close()
        return jsonify({"success": True, "message": "员工添加成功", "data": new_employee}), 201
    except sqlite3.IntegrityError as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"数据库错误：{str(e)}"}), 400
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"添加失败: {str(e)}"}), 500

@app.route('/api/employees/<employee_code>', methods=['PUT'])
def update_employee(employee_code):
    data = request.json
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    # 检查员工是否存在
    cursor.execute("SELECT * FROM employees WHERE employee_code = ?", (employee_code,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "员工不存在"}), 404

    update_fields = []
    params = []
    allowed_fields = ['name', 'gender', 'department', 'position', 'hire_date', 'phone', 'email', 'emergency_contact', 'work_experience']
    for field in allowed_fields:
        if field in data:
            update_fields.append(f"{field} = ?")
            params.append(data[field])
    if not update_fields:
        conn.close()
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400

    # 更新更新时间
    update_fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    params.append(employee_code)

    try:
        sql = f"UPDATE employees SET {', '.join(update_fields)} WHERE employee_code = ?"
        cursor.execute(sql, params)
        conn.commit()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM employees WHERE employee_code = ?", (employee_code,))
        updated_employee = dict(cursor.fetchone())
        conn.close()
        return jsonify({"success": True, "message": "员工更新成功", "data": updated_employee})
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500

@app.route('/api/employees/<employee_code>', methods=['DELETE'])
def delete_employee(employee_code):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("DELETE FROM employees WHERE employee_code = ?", (employee_code,))
    changes = conn.total_changes
    conn.commit()
    conn.close()
    if changes > 0:
        return jsonify({"success": True, "message": "员工删除成功"})
    else:
        return jsonify({"success": False, "message": "员工不存在"}), 404

# 添加操作日志查询接口
@app.route('/api/logs', methods=['GET'])
def get_logs():
    date = request.args.get('date')
    log_type = request.args.get('op_type')
    user = request.args.get('user')
    result = request.args.get('result')
    page = int(request.args.get('page', 1))
    size = int(request.args.get('size', 10))
    offset = (page - 1) * size
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    conditions = []
    params = []
    if date:
        conditions.append("timestamp LIKE ?")
        params.append(f"{date}%")
    if log_type:
        conditions.append("op_type = ?")
        params.append(log_type)
    if user:
        conditions.append("user LIKE ?")
        params.append(f"%{user}%")
    if result:
        conditions.append("result = ?")
        params.append(result)
    where = f"WHERE {' AND '.join(conditions)}" if conditions else ''
    total = cursor.execute(f"SELECT COUNT(*) FROM operation_logs {where}", params).fetchone()[0]
    query = f"SELECT * FROM operation_logs {where} ORDER BY timestamp DESC LIMIT ? OFFSET ?"
    cursor.execute(query, params + [size, offset])
    rows = cursor.fetchall()
    logs = [dict(row) for row in rows]
    conn.close()
    return jsonify({"success": True, "data": logs, "total": total})

# 登录接口
@app.route('/api/login', methods=['POST'])
def api_login():
    data = request.json
    username = data.get('username')
    password = data.get('password')
    # 简单示例，仅支持 admin/123456
    if username == 'admin' and password == '123456':
        return jsonify({"success": True, "message": "登录成功"})
    else:
        return jsonify({"success": False, "message": "用户名或密码错误"}), 401

# 添加考勤记录API路由
@app.route('/api/attendance-records', methods=['GET'])
def get_attendance_records():
    code = request.args.get('code')
    month = request.args.get('month')
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    if code and month:
        like_pattern = f"{month}-%"
        cursor.execute("SELECT * FROM attendance_records WHERE code = ? AND date LIKE ? ORDER BY date DESC, in_time DESC", (code, like_pattern))
    elif code:
        cursor.execute("SELECT * FROM attendance_records WHERE code = ? ORDER BY date DESC, in_time DESC", (code,))
    else:
        cursor.execute("SELECT * FROM attendance_records ORDER BY date DESC, in_time DESC")
    records = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return jsonify({"success": True, "data": records})

@app.route('/api/attendance-records/punch-in', methods=['POST'])
def punch_in():
    data = request.json
    code = data.get('code')
    name = data.get('name')
    date = data.get('date')
    in_time = data.get('inTime')
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    # 检查同一员工同一天只能打卡一次
    cursor.execute("SELECT COUNT(*) FROM attendance_records WHERE code = ? AND date = ?", (code, date))
    if cursor.fetchone()[0] > 0:
        conn.close()
        return jsonify({"success": False, "message": "该员工今日已打卡"}), 400
    cursor.execute(
        "INSERT INTO attendance_records (code, name, date, in_time, out_time, duration, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
        (code, name, date, in_time, '', '', now, now)
    )
    conn.commit()
    conn.close()
    return jsonify({"success": True})

@app.route('/api/attendance-records/punch-out', methods=['PUT'])
def punch_out():
    data = request.json
    code = data.get('code')
    date = data.get('date')
    out_time = data.get('outTime')
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute(
        "SELECT in_time FROM attendance_records WHERE code = ? AND date = ? AND (out_time IS NULL OR out_time = '')",
        (code, date)
    )
    row = cursor.fetchone()
    if not row:
        conn.close()
        return jsonify({"success": False, "message": "未找到打卡记录"}), 404
    in_time = row[0]
    # 计算时长
    diff = (datetime.strptime(f"{date}T{out_time}", "%Y-%m-%dT%H:%M:%S") - datetime.strptime(f"{date}T{in_time}", "%Y-%m-%dT%H:%M:%S")).total_seconds()
    duration = f"{int(diff//3600)}h {int((diff%3600)//60)}m"
    cursor.execute(
        "UPDATE attendance_records SET out_time = ?, duration = ?, update_time = ? WHERE code = ? AND date = ? AND (out_time IS NULL OR out_time = '')",
        (out_time, duration, now, code, date)
    )
    conn.commit()
    conn.close()
    return jsonify({"success": True})

# 添加考勤率计算接口
@app.route('/api/attendance-rate', methods=['GET'])
def get_attendance_rate():
    code = request.args.get('code')
    month = request.args.get('month')
    if not month:
        return jsonify({"success": False, "message": "缺少必填参数：month"}), 400
    try:
        year, mon = map(int, month.split('-'))
    except:
        return jsonify({"success": False, "message": "month格式应为YYYY-MM"}), 400
    total_days = calendar.monthrange(year, mon)[1]
    # 包含周末的总天数基数
    total_working = total_days
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    like_pattern = f"{month}-%"
    def calc_rate(employee_code):
        cursor.execute("SELECT COUNT(DISTINCT date) as present_days FROM attendance_records WHERE code = ? AND date LIKE ? AND in_time != ''", (employee_code, like_pattern))
        present = cursor.fetchone()['present_days'] or 0
        rate = round(present / total_working * 100, 2) if total_working > 0 else 0
        return present, rate

    # 如果指定code，返回单个员工
    if code:
        present, rate = calc_rate(code)
        conn.close()
        return jsonify({"success": True, "data": {"code": code, "month": month, "working_days": total_working, "present_days": present, "attendance_rate": f"{rate}%"}})
    # 统计所有员工出勤汇总
    cursor.execute("SELECT COUNT(DISTINCT date || code) as total_present FROM attendance_records WHERE date LIKE ? AND in_time != ''", (like_pattern,))
    total_present = cursor.fetchone()['total_present'] or 0
    cursor.execute("SELECT COUNT(*) as emp_count FROM employees")
    emp_count = cursor.fetchone()['emp_count'] or 0
    conn.close()
    # 计算总出勤率（出勤次数 / (员工数 × 当月天数)）
    denom = emp_count * total_days
    ratio = round(total_present / denom, 2) if denom > 0 else 0
    return jsonify({
        "success": True,
        "data": {
            "month": month,
            "employee_count": emp_count,
            "total_days": total_days,
            "total_present_days": total_present,
            "attendance_rate": ratio
        }
    })

@app.route('/api/attendance-rate/summary', methods=['GET'])
def get_attendance_rate_summary():
    month = request.args.get('month')
    if not month:
        return jsonify({"success": False, "message": "缺少必填参数：month"}), 400
    try:
        year, mon = map(int, month.split('-'))
    except:
        return jsonify({"success": False, "message": "month格式应为YYYY-MM"}), 400
    total_days = calendar.monthrange(year, mon)[1]
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    like_pattern = f"{month}-%"
    # 员工总数
    cursor.execute("SELECT COUNT(*) as emp_count FROM employees")
    emp_count = cursor.fetchone()['emp_count'] or 0
    # 总出勤记录数（每天每位员工算一次）
    cursor.execute(
        "SELECT COUNT(DISTINCT date || code) as total_present FROM attendance_records WHERE date LIKE ? AND in_time != ''",
        (like_pattern,)
    )
    total_present = cursor.fetchone()['total_present'] or 0
    conn.close()
    # 计算总出勤率（出勤次数 / (员工数 × 当月天数)）
    denom = emp_count * total_days
    ratio = round(total_present / denom, 2) if denom > 0 else 0
    return jsonify({
        "success": True,
        "data": {
            "month": month,
            "employee_count": emp_count,
            "total_days": total_days,
            "total_present_days": total_present,
            "attendance_rate": ratio
        }
    })

# --------- 采购订单管理模块 API 路由开始 ---------
@app.route('/api/purchase-orders', methods=['GET'])
def get_purchase_orders():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT order_number, supplier, order_date, total_amount, status FROM purchase_orders ORDER BY create_time DESC")
    orders = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return jsonify({"success": True, "data": orders})

@app.route('/api/purchase-orders/<order_number>', methods=['GET'])
def get_purchase_order(order_number):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM purchase_orders WHERE order_number = ?", (order_number,))
    order = cursor.fetchone()
    if not order:
        conn.close()
        return jsonify({"success": False, "message": "订单不存在"}), 404
    order_dict = dict(order)
    cursor.execute("SELECT * FROM purchase_items WHERE order_number = ?", (order_number,))
    items = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return jsonify({"success": True, "data": {"order": order_dict, "items": items}})

@app.route('/api/purchase-orders', methods=['POST'])
def create_purchase_order():
    data = request.json
    supplier = data.get('supplier')
    order_date = data.get('order_date')
    expected_date = data.get('expected_date')
    items = data.get('items', [])
    if not supplier or not order_date or not items:
        return jsonify({"success": False, "message": "缺少必填字段"}), 400
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    # 生成订单号
    current_date = datetime.now().strftime("%Y%m%d")
    cursor.execute("SELECT COUNT(*) FROM purchase_orders WHERE order_number LIKE ?", (f"PO{current_date}%",))
    count = cursor.fetchone()[0]
    order_number = f"PO{current_date}{str(count+1).zfill(4)}"
    total_amount = sum(item.get('quantity', 0) * item.get('unit_price', 0) for item in items)
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    status = 'pending'
    try:
        cursor.execute(
            """
            INSERT INTO purchase_orders (order_number, supplier, order_date, expected_date, total_amount, status, create_time, update_time)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (order_number, supplier, order_date, expected_date, total_amount, status, now_str, now_str)
        )
        for item in items:
            cursor.execute(
                """
                INSERT INTO purchase_items (order_number, product_code, product_name, quantity, unit_price, note, create_time)
                VALUES (?, ?, ?, ?, ?, ?, ?)""",
                (order_number, item.get('product_code'), item.get('product_name'),
                 item.get('quantity'), item.get('unit_price'), item.get('note', ''), now_str)
            )
        conn.commit()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT order_number, supplier, order_date, total_amount, status FROM purchase_orders WHERE order_number = ?", (order_number,))
        order_summary = dict(cursor.fetchone())
        return jsonify({"success": True, "message": "订单创建成功", "data": order_summary})
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"创建失败: {str(e)}"}), 500
    finally:
        conn.close()

@app.route('/api/purchase-orders/<order_number>', methods=['PUT'])
def update_purchase_order(order_number):
    data = request.json
    status = data.get('status')
    if status not in ['pending', 'completed']:
        return jsonify({"success": False, "message": "状态无效"}), 400
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM purchase_orders WHERE order_number = ?", (order_number,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "订单不存在"}), 404
    try:
        cursor.execute("UPDATE purchase_orders SET status = ?, update_time = ? WHERE order_number = ?", (status, now_str, order_number))
        conn.commit()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT order_number, supplier, order_date, total_amount, status FROM purchase_orders WHERE order_number = ?", (order_number,))
        order_summary = dict(cursor.fetchone())
        return jsonify({"success": True, "message": "状态更新成功", "data": order_summary})
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500
    finally:
        conn.close()

@app.route('/api/purchase-orders/<order_number>', methods=['DELETE'])
def delete_purchase_order(order_number):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM purchase_orders WHERE order_number = ?", (order_number,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "订单不存在"}), 404
    try:
        cursor.execute("DELETE FROM purchase_items WHERE order_number = ?", (order_number,))
        cursor.execute("DELETE FROM purchase_orders WHERE order_number = ?", (order_number,))
        conn.commit()
        return jsonify({"success": True, "message": "删除成功"})
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
    finally:
        conn.close()
# --------- 采购订单管理模块 API 路由结束 ---------

@app.route('/api/purchase-orders/<order_number>/export', methods=['GET'])
def export_purchase_order(order_number):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    # 获取订单
    cursor.execute('SELECT * FROM purchase_orders WHERE order_number = ?', (order_number,))
    order = cursor.fetchone()
    if not order:
        conn.close()
        return jsonify({'success': False, 'message': '订单不存在'}), 404
    order = dict(order)
    # 获取订单明细
    cursor.execute('SELECT * FROM purchase_items WHERE order_number = ?', (order_number,))
    items = [dict(row) for row in cursor.fetchall()]
    conn.close()
    # 生成CSV
    output = io.StringIO()
    writer = csv.writer(output)
    # 写入订单信息
    writer.writerow(['订单号', '供应商', '订单日期', '预计到货日期', '总金额', '状态'])
    writer.writerow([order['order_number'], order['supplier'], order['order_date'], order.get('expected_date', ''), order['total_amount'], order['status']])
    writer.writerow([])
    # 写入商品明细
    writer.writerow(['商品编码', '商品名称', '数量', '单价', '备注'])
    for it in items:
        writer.writerow([it['product_code'], it['product_name'], it['quantity'], it['unit_price'], it.get('note', '')])
    csv_data = output.getvalue()
    output.close()
    # 返回文件
    return Response(
        csv_data,
        mimetype='text/csv',
        headers={
            'Content-Disposition': f'attachment; filename={order_number}.csv'
        }
    )

# --------- 薪资管理模块开始 ---------
def init_salary_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS salaries (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        employee_code TEXT NOT NULL,
        department TEXT NOT NULL,
        base_salary REAL,
        bonus REAL,
        deductions REAL,
        net_salary REAL,
        status TEXT NOT NULL,
        create_time TEXT NOT NULL,
        update_time TEXT NOT NULL,
        FOREIGN KEY(employee_code) REFERENCES employees(employee_code)
    )
    ''')
    conn.commit()
    conn.close()

# 初始化薪资数据库
init_salary_db()

# 添加 month 列（如果不存在）并回填已有记录
conn = sqlite3.connect(DATABASE)
cursor = conn.cursor()
try:
    cursor.execute("ALTER TABLE salaries ADD COLUMN month TEXT")
except sqlite3.OperationalError:
    pass
cursor.execute("UPDATE salaries SET month = substr(create_time, 1, 7) WHERE month IS NULL")
conn.commit()
conn.close()

# 获取所有薪资记录
@app.route('/api/salaries', methods=['GET'])
def get_salaries():
    month = request.args.get('month')
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    if month:
        cursor.execute(
            """
            SELECT e.employee_code, e.name, e.department,
                   s.id, s.base_salary, s.bonus, s.deductions, s.net_salary,
                   s.month,
                   COALESCE(s.status, '未发放') AS status
            FROM employees e
            LEFT JOIN salaries s ON e.employee_code = s.employee_code AND s.month = ?
            ORDER BY e.department, e.name
            """,
            (month,)
        )
    else:
        cursor.execute(
            """
            SELECT e.employee_code, e.name, e.department,
                   s.id, s.base_salary, s.bonus, s.deductions, s.net_salary,
                   s.month,
                   COALESCE(s.status, '未发放') AS status
            FROM employees e
            LEFT JOIN salaries s ON e.employee_code = s.employee_code
            ORDER BY e.department, e.name
            """
        )
    rows = cursor.fetchall()
    data = []
    rule = get_deduct_rule()
    late_deduct = rule.get('late', 50)
    early_deduct = rule.get('early', 50)
    absent_deduct = rule.get('absent', 100)
    for row in rows:
        item = dict(row)
        base = item.get('base_salary') or 0
        bonus = item.get('bonus') or 0
        deduction = 0
        absent_days = 0
        if month and item.get('employee_code'):
            like_pattern = f"{month}-%"
            cursor.execute(
                "SELECT in_time, out_time FROM attendance_records WHERE code = ? AND date LIKE ?",
                (item['employee_code'], like_pattern)
            )
            recs = cursor.fetchall()
            for rec in recs:
                in_time = rec['in_time'] or ''
                out_time = rec['out_time'] or ''
                # 迟到且早退
                if in_time > '09:00:00' and out_time < '18:00:00':
                    deduction += late_deduct + early_deduct
                # 仅迟到
                elif in_time > '09:00:00':
                    deduction += late_deduct
                # 仅早退
                elif out_time and out_time < '18:00:00':
                    deduction += early_deduct
            # 缺勤天数统计：当月总天数 - 打卡天数（无记录视为缺勤）
            try:
                y, m = map(int, month.split('-'))
                days_in_month = calendar.monthrange(y, m)[1]
            except:
                days_in_month = 0
            absent_days = max(0, days_in_month - len(recs))
            deduction += absent_days * absent_deduct
            # 扣款项不超过基本工资
            deduction = min(deduction, base)
        item['deductions'] = deduction
        item['net_salary'] = base + bonus - deduction
        item['absent_days'] = absent_days
        data.append(item)
    conn.close()
    return jsonify({"success": True, "data": data})

# 获取单个薪资记录
@app.route('/api/salaries/<int:salary_id>', methods=['GET'])
def get_salary(salary_id):
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT s.*, e.name FROM salaries s JOIN employees e ON s.employee_code = e.employee_code WHERE s.id = ?", (salary_id,))
    row = cursor.fetchone()
    conn.close()
    if row:
        return jsonify({"success": True, "data": dict(row)})
    else:
        return jsonify({"success": False, "message": "薪资记录不存在"}), 404

# 创建薪资记录（初始状态未发放）
@app.route('/api/salaries', methods=['POST'])
def create_salary():
    data = request.json
    if 'employee_code' not in data or not data['employee_code']:
        return jsonify({"success": False, "message": "缺少必填字段：employee_code"}), 400
    if 'month' not in data or not data['month']:
        return jsonify({"success": False, "message": "缺少必填字段：month"}), 400
    employee_code = data['employee_code']
    month = data['month']
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT department FROM employees WHERE employee_code = ?", (employee_code,))
    emp = cursor.fetchone()
    if not emp:
        conn.close()
        return jsonify({"success": False, "message": "员工不存在"}), 404
    department = emp[0]
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    try:
        cursor.execute(
            "INSERT INTO salaries (employee_code, department, base_salary, bonus, deductions, net_salary, status, create_time, update_time, month) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (employee_code, department, None, None, None, None, '未发放', now, now, month)
        )
        conn.commit()
        salary_id = cursor.lastrowid
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM salaries WHERE id = ?", (salary_id,))
        salary = dict(cursor.fetchone())
        conn.close()
        return jsonify({"success": True, "message": "薪资记录创建成功", "data": salary}), 201
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"创建失败: {str(e)}"}), 500

# 更新薪资记录（包括薪资详情及状态切换）
@app.route('/api/salaries/<int:salary_id>', methods=['PUT'])
def update_salary(salary_id):
    data = request.json
    fields = []
    params = []
    for key in ['base_salary', 'bonus', 'deductions', 'net_salary', 'status']:
        if key in data:
            fields.append(f"{key} = ?")
            params.append(data[key])
    if not fields:
        return jsonify({"success": False, "message": "没有提供要更新的字段"}), 400
    if 'status' in data and data['status'] not in ['已发放', '未发放']:
        return jsonify({"success": False, "message": "状态无效"}), 400
    fields.append("update_time = ?")
    params.append(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    params.append(salary_id)
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    try:
        cursor.execute(f"UPDATE salaries SET {', '.join(fields)} WHERE id = ?", params)
        conn.commit()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM salaries WHERE id = ?", (salary_id,))
        salary = dict(cursor.fetchone())
        conn.close()
        return jsonify({"success": True, "message": "更新成功", "data": salary})
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"更新失败: {str(e)}"}), 500

# 删除薪资记录
@app.route('/api/salaries/<int:salary_id>', methods=['DELETE'])
def delete_salary(salary_id):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM salaries WHERE id = ?", (salary_id,))
    if not cursor.fetchone():
        conn.close()
        return jsonify({"success": False, "message": "薪资记录不存在"}), 404
    try:
        cursor.execute("DELETE FROM salaries WHERE id = ?", (salary_id,))
        conn.commit()
        conn.close()
        return jsonify({"success": True, "message": "删除成功"})
    except Exception as e:
        conn.rollback()
        conn.close()
        return jsonify({"success": False, "message": f"删除失败: {str(e)}"}), 500
# --------- 薪资管理模块结束 ---------

# 添加薪资表导出功能
@app.route('/api/salaries/export', methods=['GET'])
def export_salaries():
    month = request.args.get('month')
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    if month:
        cursor.execute(
            "SELECT e.name as employee_name, e.department, s.base_salary, s.bonus, s.deductions, s.net_salary, COALESCE(s.status, '未发放') as status "
            "FROM employees e LEFT JOIN salaries s ON e.employee_code = s.employee_code AND s.month = ? "
            "ORDER BY e.department, e.name",
            (month,)
        )
    else:
        cursor.execute(
            "SELECT e.name as employee_name, e.department, s.base_salary, s.bonus, s.deductions, s.net_salary, COALESCE(s.status, '未发放') as status "
            "FROM employees e LEFT JOIN salaries s ON e.employee_code = s.employee_code "
            "ORDER BY e.department, e.name"
        )
    rows = cursor.fetchall()
    conn.close()
    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(['员工姓名', '部门', '基本工资', '奖金', '扣款项', '实发工资', '发放状态'])
    for row in rows:
        writer.writerow([
            row['employee_name'], row['department'],
            row['base_salary'] or 0, row['bonus'] or 0,
            row['deductions'] or 0, row['net_salary'] or 0,
            row['status']
        ])
    csv_data = output.getvalue()
    output.close()
    filename = f"salaries_{month}.csv" if month else "salaries_all.csv"
    return Response(
        csv_data,
        mimetype='text/csv',
        headers={'Content-Disposition': f'attachment; filename={filename}'}
    )

# 扣款规则API
@app.route('/api/deduct-rule', methods=['POST'])
def api_save_deduct_rule():
    data = request.json
    late = data.get('late')
    early = data.get('early')
    absent = data.get('absent')
    if late is None or early is None or absent is None:
        return jsonify({'success': False, 'message': '参数缺失'}), 400
    try:
        save_deduct_rule(late, early, absent)
        return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500

@app.route('/api/deduct-rule', methods=['GET'])
def api_get_deduct_rule():
    rule = get_deduct_rule()
    return jsonify({'success': True, 'data': rule})

@app.route('/api/point-rule', methods=['POST'])
def api_save_point_rule():
    data = request.json
    rate = data.get('rate')
    if rate is None:
        return jsonify({'success': False, 'message': '参数缺失'}), 400
    try:
        save_point_rule(rate)
        return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)}), 500

@app.route('/api/point-rule', methods=['GET'])
def api_get_point_rule():
    rule = get_point_rule()
    return jsonify({'success': True, 'data': rule})

# 添加系统维护导出功能
BACKUP_INFO_FILE = os.path.join(BASE_DIR, 'data', 'backup_info.json')

@app.route('/api/backup/export', methods=['GET'])
def export_database():
    ts = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    try:
        with open(BACKUP_INFO_FILE, 'w', encoding='utf-8') as f:
            json.dump({'last_backup': ts}, f, ensure_ascii=False)
    except:
        pass
    directory = os.path.join(BASE_DIR, 'data')
    filename = os.path.basename(DATABASE)
    return send_from_directory(directory, filename, as_attachment=True)

@app.route('/api/backup/info', methods=['GET'])
def get_backup_info():
    last_backup = ''
    if os.path.exists(BACKUP_INFO_FILE):
        try:
            with open(BACKUP_INFO_FILE, 'r', encoding='utf-8') as f:
                last_backup = json.load(f).get('last_backup', '')
        except:
            last_backup = ''
    return jsonify({'success': True, 'last_backup': last_backup})

@app.route('/api/logs/export', methods=['GET'])
def export_logs():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM operation_logs ORDER BY timestamp DESC")
    rows = cursor.fetchall()
    columns = [desc[0] for desc in cursor.description]
    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(columns)
    for row in rows:
        writer.writerow([row[col] for col in columns])
    conn.close()
    csv_data = output.getvalue()
    output.close()
    return Response(csv_data, mimetype='text/csv', headers={'Content-Disposition': 'attachment; filename=operation_logs.csv'})

@app.route('/api/logs/info', methods=['GET'])
def get_logs_info():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT MAX(timestamp) FROM operation_logs")
    last_log = cursor.fetchone()[0] or ''
    conn.close()
    return jsonify({'success': True, 'last_log': last_log})

if __name__ == '__main__':
    app.run(debug=True, port=5000) 